SQL: Where MYID = ANY?

问题:

In a SQL query, that contains

... WHERE MYID = @1 ....

I have to manage 2 cases
1) There is a filter on a column, @1 will be a number (1,2,X...)

2) There is no filter on that column, @1 will be ...? (ANY)

Is there something for this "any" (SQL Server 2005) ?

PS.
Obviously, I understand that I can remove the "where".

PPS.
I explain myself for better understanding: I have this query in the code, and would like to pass an integer as parameter when the filter is ON, and "something" when my filter is OFF.

if (filterOn)
    GetFoos(fooID);
else
    GetFoos("ANY");

GetFoos(param1): "select * from FOOS where FOOID = {0}", param1

回答1:

Make a UNION ALL of the two statements:

SELECT  *
FROM    mytable
WHERE   myid = @col
UNION ALL
SELECT  *
FROM    mytable
WHERE   @col IS NULL

or just split them in an IF / ELSE block of a stored procedures.

Either way, the optimizer will be able to optimize the queries separately, completely ignoring one of them depending on the value of @col.

回答2:

you could do something along this line:

 where (myid = @id or @id is null)

so you will only filter when @id contains a value and not when it is null.

回答3:

Just remove the where or and clause if there is no filter on that column.

回答4:

If you don't want to filter on a particular column, just don't filter on that column. Remove the WHERE MYID = entirely

回答5:

Having

"select * from FOO where FOOID = {0}", param

use param="FOOID" when there is no param to filter, this will give

select * from FOO where FOOID = FOOID // removing the filter