Null parameters in stored procedure

Question

What is the best method of handling null parameters in stored procedure ?

I have a stored procedure with 3 input parameters, any one of them parameter can be null so how to handle those parameter.

SP_GetDetails input parameter (varchar p1, varchar p2, int p3, datetime p4, datetime p5)

In the stored procedure, there are different query based on input parameters.

if(p1<>null)
///
else
 if(p2<>null)
 ///
 else
  if()... so on...

So my question: Is it good to have n number of if condition.

Please provide an example. The information in this question is insufficient. — Zephyr
– Zephyr, Commented Oct 19, 2010 at 10:50

Martin Smith · Accepted Answer · 2010-10-19 11:01:12Z

1

Your question is missing a lot of detail. I suspect this might be of help though. Dynamic Search Conditions in T-SQL

answered Oct 19, 2010 at 11:01

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sachin Shanbhag · Accepted Answer · 2010-10-19 11:39:34Z

0

The using of multiple If statements in stored procs detract from the readability of the code. Also, the maintainability of the code suffers. Instead you can use CASE statements.

answered Oct 19, 2010 at 11:39

Sachin Shanbhag

55.7k11 gold badges92 silver badges103 bronze badges

Collectives™ on Stack Overflow

Null parameters in stored procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related