Ignoring a NULL parameter in T-SQL

I would handle it this way.

WHERE Thing = ISNULL(@Thing, Thing)

If you're just using the parameter as a filter on the where clause, this will work very well. It will ignore the parameter if it is null.


I generally use

WHERE (id = @id OR @id IS NULL)
AND (num = @num OR @num IS NULL)

etc.


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).