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.

possible duplicate of [How can I use optional parameters in a T-SQL stored procedure?](http://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure) — Victor Zakharov, Mar 25 '15 at 18:19

score 5 · Answer 1 · edited Jun 20 '20 at 09:12

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

you should expect better performance using `OPTION(RECOMPILE)` where available if you're passing `NULL` values... seems like comment tag between questions :D — Matthew, Jan 20 '11 at 17:53

score 5 · Accepted Answer · answered Jan 20 '11 at 10:15

5

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.

answered Jan 20 '11 at 10:15

Jan_V

4,244
1
40
64

This is exactly the kind of trick I'm talking about in my answer! – Martin Smith Jan 20 '11 at 10:18
2 comments deleted. at this point in time, fully agree with Martin. Very prone to parameter sniffing. – RichardTheKiwi Jan 20 '11 at 11:54

score 1 · Answer 3 · answered Jan 20 '11 at 10:19

if it's a stored procedure, either you do with dynamic SQL and do not append the where clause at all if parameters are null, or you still use an IF ELSE and write the query twice in the IF and in the else one with the where and one without, I agree with Martin that the where should be fully avoided if all records should be retrieved.

score 1 · Answer 4 · answered Jan 20 '11 at 10:19

1

...
WHERE
    (
        col1 IS NULL
        AND col2 IS NULL
        AND col3 IS NULL
    ) OR
    (
        conditions...
    );

answered Jan 20 '11 at 10:19

mingos

23,778
12
70
107

RichardTheKiwi · Answer 5 · 2011-01-27T20:43:42.370

1

Have a look here handling-optional-parameters for an article fitting your requirements. The article compares various ways of doing optional parameters and discusses different versions of SQL Server as well as the performance of each.

I think what you are after is an individual IS NULL + OR per column, right?

WHERE (@col1 IS NULL OR col1 LIKE @col1)
  AND (@col2 IS NULL OR col2 = @col2)

edited Jan 27 '11 at 20:43

answered Jan 20 '11 at 11:00

RichardTheKiwi

105,798
26
196
262

1

That is in the demonstration of what not to do! Read further "The best method is to handle them in code, create two procedures one for each scenario. In case that is not possible, (if you are trying to improve performance of a piece written some time back and you do not have the luxury of changing the application code),branch the code within stored procedure." – Martin Smith Jan 20 '11 at 11:05
@Martin - I posted that link for both options (because it has the code samples already). I don't agree with the first and am constructing tests to disprove it. – RichardTheKiwi Jan 20 '11 at 11:20
@cyberkiwi - Good luck with that! – Martin Smith Jan 20 '11 at 11:21
I read an article a year back and tested it on 2008.. but whatever I do now only seems to validate the article.. – RichardTheKiwi Jan 20 '11 at 11:47
@cyberkiwi - Were you definitely using parameters not constants? If so do you remember if you were using `option (recompile)` (as I said the behaviour of this has varied) – Martin Smith Jan 20 '11 at 11:51
@cyberkiwi - I just found out that the `option(recompile)` hint [will work for this](http://www.sommarskog.se/dyn-search.html) as long as the version is at least `SP1 CU5`. At RTM it worked, then they removed the functionality subsequently due to a bug but now it works again. So this could be used to avoid unnecessary scans at the expense of recompiling the statement on each invocation. – Martin Smith Jan 20 '11 at 12:38
@Martin - Thank you so much! I have been looking for that link since `forever` and a day ago. The article has evolved a bit since I last read it, probably tempered with MS constantly tweaking the behaviour of IS NULL + OR. – RichardTheKiwi Jan 20 '11 at 18:27

score 1 · Answer 6 · answered Jan 20 '11 at 11:56

1

i really think this will work

    Where ((CASE WHEN @Parameter1 is null then 1 else 0 end) = 1 and 
   (CASE WHEN @Parameter2 is null then 1 else 0 end) = 1)

answered Jan 20 '11 at 11:56

Andrew

308
1
8

SQL: Cancel 'where' on null parameter

6 Answers6

Edit:

Linked