SQL: Cancel 'where' on null parameter

Question

This may be obvious but I'm getting very confused.

I have an SQL query with a where clause (where in with a list of parameters). If all of these parameters are null, I need the SQL to ignore the where clause and retrieve all the records. Is this easy to do in SQL? I know one way around it is to just remove the where clause using code if the parameters are null.

Answer 1

You could try do something like this:

select *
from foo
where (@parameter1 is null AND @parameter2 is null)
OR  (@parameter1 = 'value1'
    AND
    @parameter2 = 'value2')

Offcourse it needs a bit of tuning in your own query, but now you will check if the parameters are null or do your original where-clause.

Answer 2

The most performant way is to not include the WHERE clause at all if that's an option for you.

You often see tricks such as WHERE X=@X OR @X IS NULL used but these can lead to sub optimal plans and unnecessary table scans in the event you are passing a specific value for @X

Edit:

As this answer seems to have met with some unexpected scepticism...

create table #t
(
id varchar(5) primary key /*varchar to test LIKE without causing any casts*/
)

INSERT INTO #t
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM sys.all_columns


SET STATISTICS IO ON
/*Test the equals */

EXEC sp_executesql N'
SELECT * 
FROM #t
WHERE (@id IS NULL OR id = @id)', N'@id varchar(5)', @id='1'

/*Is `LIKE` any better? */

EXEC sp_executesql N'
SELECT * 
FROM #t
WHERE (@id IS NULL OR id LIKE @id)', N'@id varchar(5)', @id='1'

/*What should the plan look like? */

EXEC sp_executesql N'
SELECT * 
FROM #t
WHERE (id = @id)', N'@id varchar(5)', @id='1'

DROP TABLE #t