performance - Ignoring a NULL parameter in T-SQL

Question

I want to be able to pass in a list of parameters, and ignore the ones which are NULL. So that the query is in effect pretending that the filter isn't there and ignoring it.

I was doing it like this:

(@thing IS NULL or Thing=@thing) 

Is this right, and if so, would it perform badly? It's seems to be a lot slower than constructing the SQL separately.

What's the optimal way to do this?

FIXED! See Marc Gravell's answer. In summary using IS NULL many times is a big performance hit.

Answer 1

Once you get more than a couple of these, then yes: it starts to get pretty slow. In such cases, I tend to use generated TSQL - i.e.

DECLARE @sql nvarchar(4000)
SET @sql = /* core query */

IF @name IS NOT NULL
    SET @sql = @sql + ' AND foo.Name = @name'

IF @dob IS NOT NULL
    SET @sql = @sql + ' AND foo.DOB = @dob'

// etc

EXEC sp_ExecuteSQL @sql, N'@name varchar(100), @dob datetime',
        @name, @dob

etc

Note that sp_ExecuteSQL caches query-plans, so any queries with the same args can potentially re-use the plan.

The downside is that unless you sign the SPROC, the caller needs SELECT permissions on the table (not just EXEC permissions on the SPROC).