SQL: Cancel 'where' on null parameter

问题:

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.

回答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.

回答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

回答3:

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.

回答4:

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

回答5:

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)

回答6:

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)