CASE statement in WHERE clause

Question

i have a procedure in which the below condition is to be written in a WHERE clause. How do I do that using CASE, or something like it?

IF (@itemId IS NOT NULL)
  dataId = @itemid 

Answer 1

Case When @ItemId Is Not Null And dataId = itemId Then 1 Else 0 End = 0

If dataId is not nullable, then you can simplify it to:

dataId = Coalesce(@ItemId, dataId) 

Or using a Case expression

dataId = Case When @ItemId Is Null Then itemId Else dataId End

Answer 2

You don't need a case for that:

... where @itemId is null or dataId = @itemId ...

Or:

... where isnull(@itemId, dataId) = dataId ...

Answer 3

go on, read the msdn :)

otherwise eg.

SELECT *
FROM   [testTable]
WHERE  ISNULL(@itemId, [dataId]) = [dataId]

please elaborate your sepcificatons!

Answer 4

You can do something like this:

SELECT dataId = CASE WHEN @itemId IS NOT NULL THEN @itemId 
        ELSE dataId END
 FROM YourTable