Conditional Check 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.

itemid is a parameter which can be null.

if itemid is available then add it to my where clause,else do nothing

Answer 1

Some people use this technique

... WHERE @itemid IS NULL OR tbl.itemid = @itemid

It guarantees though that you will never get an index seek on the itemid column.

A better approach if the table is at all big is to split the query up into 2 separate cases

IF(@itemid IS NULL)
  SELECT foo FROM bar
ELSE
  SELECT foo FROM bar WHERE itemid = @itemid

If the number of combinations is too large you can consider dynamic SQL. Be sure you understand SQL injection first.

Ref: Dynamic Search Conditions in T-SQL

Answer 2

e.g.

SELECT Something
FROM SomeTable
WHERE (@MyParam IS NULL OR SomeField = @MyParam)
    AND AnotherField = 1

You'll want to test this in your specific scenario for performance. If it's a simple query i.e. without a lot of conditional parameters, you might want to try this instead for performance:

IF ( @MyParam IS NULL )
    SELECT Something
    FROM SomeTable
    WHERE AnotherField = 1
ELSE
    SELECT Something
    FROM SomeTable
    WHERE SomeField = @MyParam 
        AND AnotherField = 1