Using NULL to short-circuit | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Using NULL to short-circuit

I need to create a sproc that allows for filtering on specific columns that can change based on the users desires. I do have a known and finite set of columns to use. Suppose that for each column there is a parameter that accepts a value or defaults to null. Is the following query WHERE clause below efficient? SELECT column1, column2, column3
FROM tablename
WHERE (@param1 IS NULL OR column1 = @param1) AND
(@param2 IS NULL OR column2 = @param2) AND
(@param3 IS NULL OR column3 = @param3) The idea is that the ORs will short-circuit before testing the column values. Also, will SQL Server’s optimizer know that the parameters do not change value, so if they are null to start with, there is no reason to even evaluate the expressions? If not, is there some way to give a hint so that it will?
I would reference the following discussion, we went into a fairly lengthy discussion about this recently. http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=708 Be sure to look farther down in the thread, the information you want to know is there.
]]>