CODEWITHSUNDEEP
t-sqlnullwhere-clause
salvationishere
salvationishere

Reputation: 3511

Creating a T-sQL where clause to accept NULL values

Now I am developing a stored proc which takes 3 input parameters. But the challenge is that the customer doesn't have to enter any of these parameters; they may instead have these 3 as nulls. How can I write this into the WHERE clause?

pseudocode is something like:

WHERE (@p1 = a.p1 or @p1 = '') 
and (@p2 = a.p2 or @p2 = '') 
and (@p2 = a.p2 or @p2 = '')

The above logic works when they choose all 3 parameters, but otherwise it returns 0 records.

Upvotes: 2

Views: 811

Answers (3)

Paulo Santos
Paulo Santos

Reputation: 11567

you should change the query to something like this:

WHERE (a.p1 = IsNull(NullIf(@p1, ''), a.p1))  
  and (a.p2 = IsNull(NullIf(@p2, ''), a.p2))
  and (a.p3 = IsNull(NullIf(@p3, ''), a.p3))

Upvotes: 1

Chandu
Chandu

Reputation: 82903

Try this:

  WHERE (@p1 = a.p1 or @p1 = '' OR @p1 IS NULL) 
    AND (@p2 = a.p2 or @p2 = '' OR @p2 IS NULL) 
    AND (@p2 = a.p2 or @p2 = '' OR @p3 IS NULL)

Upvotes: 1

p.campbell
p.campbell

Reputation: 100567

It looks like you're checking for blank/empty string (meaning 'skip this'). If the variable is also nullable, perhaps use an ISNULL(x,'') to force the null argument to be converted to an empty string.

 WHERE (@p1 = a.p1 or ISNULL(@p1,'') = '')
 AND (@p2 = a.p2   or ISNULL(@p2,'') = '') 
 AND (@p2 = a.p2   or ISNULL(@p2,'') = '')

Upvotes: 4

Related Questions