CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Serdia
Serdia

Reputation: 4418

How to tweak WHERE clause to select all records when passing NULL as a parameter in SQL statement

If @reasonID = 1 I need to select only policies that have reasonID = 211

If @reasonID = 2 I need to select only policies that have reasonID <> 211

If @reasonID = NULL I need to select all policies including NULL

In the below example it works for @reasonID = 1 and @reasonID = 2.

But how can I tweak WHERE clause to select all rows when @reasonID = NULL ?

      declare @TempTable table (PolicyNumber varchar(50), reasonID int)
        insert into @TempTable values ('Pol1', 5),
                                      ('Pol2', NULL),
                                      ('Pol3', 211),
                                      ('Pol4', 8),
                                      ('Pol5', 211),
                                      ('Pol6', NULL)

   --input @reasonID parameter     
  declare @reasonID int = 2
  select PolicyNumber,reasonID
  from @TempTable
  where 
    (case when reasonID = 211 then 1 else 2 end = @reasonID) --works fine
    OR (@reasonID = NULL)          --does not work

Upvotes: 1

Views: 273

Answers (1)

el toro
el toro

Reputation: 532

You use IS NULL not = NULL.

Your last line should be:

OR (@reasonID IS NULL)

And, if you ever want to do the reverse, the syntax is IS NOT NULL.

Upvotes: 6

Related Questions