CODEWITHSUNDEEP
sql-server
Lajith
Lajith

Reputation: 1877

Achieve where condition and case statement if null

Below is my where condition in query

WHERE ResetID= CASE WHEN  NOT EXISTS (select  1 FROM ops.table where LabelItemId=@Item) THEN NULL  
                              WHEN @LastIssued < @LastDispatched THEN  1                                                           
                              WHEN  @LastIssued >= @LastDispatched  THEN  2
                              END

If case statement returns null the where condition will not work.. Any alternate solution for this condition without case

Upvotes: 0

Views: 31

Answers (1)

Thom A
Thom A

Reputation: 95949

If you want to have NULL = NULL, you need to handle it with IS NULL. At a guess, what you want is this:

WHERE (ResetID = CASE WHEN NOT EXISTS (select  1 FROM ops.table where LabelItemId=@Item) THEN NULL  
                      WHEN @LastIssued < @LastDispatched THEN  1                                                           
                      WHEN @LastIssued >= @LastDispatched  THEN  2
                 END
   OR (ResetID IS NULL AND NOT EXISTS (select  1 FROM ops.table where LabelItemId=@Item)))

Edit note that this does require 2 scans of the table ops.table; there may be a better way to do this, but difficult to suggest when we only have a snippet.

Upvotes: 2

Related Questions