SQL query where parameters null not null

Question

I am trying to do a SQL query and to build the where condition dynamically depending if the parameters are null or no. I have something like this:

SELECT tblOrder.ProdOrder, tblOrder.Customer FROM tblOrder 
CASE WHEN @OrderId IS NOT NULL
THEN 
WHERE tblOrder.OrderId = @OrderId
ELSE
END
CASE WHEN @OrderCustomer IS NOT NULL
THEN
AND tblOrder.OrderCustomer = @OrderCustomer
ELSE
END
END

This doesn't work, but this is just a small prototype how to assemble the query, so if the orderid is not null include in the where clause, or if the ordercustomer is not null include in the where clause. But I see problem here, for example if the ordercustomer is not null but the orderid is null, there will be error because the where keyword is not included.

How can I tackle this problem?

Answer 1

This should do what you want:

SELECT tblOrder.ProdOrder, tblOrder.Customer 
FROM tblOrder 
WHERE ( @OrderId IS NULL OR  tblOrder.OrderId = @OrderId )
AND   ( @OrderCustomer IS NULL OR  tblOrder.OrderCustomer = @OrderCustomer )
OPRION (RECOMPILE)

But as commented you should include the OPTION RECOMPILE hint, otherwise it will have bad performance.

Worth reading:

• http://www.sommarskog.se/dyn-search-2008.html