SQL: Undo 'where' on null parameter

Question

It may be obvious, but I'm very confused.

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

Answer 1

You can try to do something like this:

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

In any case, it needs some tweaking in your own request, but now you will check if the parameters are null or the initial conditions where-where are satisfied.

Answer 2

The most efficient way is to not include the WHERE at all, if this is an option for you.

You often see tricks such as WHERE X=@X OR @X IS NULL , but this can lead to under optimal plans and unnecessary table scans in case you pass a specific value to @X

Edit:

It seems that this answer met some unexpected skepticism ...

 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 

Answer 3

if it is a stored procedure, either you use dynamic SQL, or don’t add the where clause at all, if the parameters are zero, or you are still using IF ELSE, and write the query in IF twice, and in the other where and where not, I agree Martin is that where it should be completely avoided if all records are to be obtained.

Answer 4

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

Answer 5

See here the processing options — optional for an article that meets your requirements. This article compares various ways to perform optional parameters and discusses different versions of SQL Server, as well as the performance of each.

I think you have a separate IS NULL + OR per column, right?

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

Answer 6

I really think it 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)