SQL WHERE 子句中的可选参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10185638/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Optional Arguments in WHERE Clause
提问by Control Freak
Lets suppose there is a stored procedure that has 3 params. Out of all the possibilities, I'm looking to achieve this with a single WHEREclause without getting out of control with using () AND () OR ()too much...
假设有一个有 3 个参数的存储过程。在所有的可能性中，我希望用一个WHERE子句来实现这一点，而不会因为使用() AND () OR ()太多而失去控制......
Example:
例子：
//Params
@CITY VARCHAR(100) = NULL,
@GENDER VARCHAR(100) = NULL,
@AGE VARCHAR(100) = NULL
I suppose you can do it using IF BEGIN ... ENDfor each Variable if Exists, but that makes the code alot longer than desired..
我想你可以IF BEGIN ... END对每个变量使用if Exists来做到这一点，但这会使代码比预期的要长得多。
This method below won't work because its way too long (there are about 10 different fields like this, but the example is only 3.) and i'm not sure if it even directly pulls up distinctive values...
下面的这个方法行不通，因为它的方法太长了（大约有 10 个这样的不同字段，但示例只有 3 个。）而且我不确定它是否甚至直接提取了独特的值......
SELECT NAME FROM TABLE
WHERE (
(CITY=@CITY AND GENDER=@GENDER AND AGE=@AGE)
OR (CITY=@CITY AND GENDER=@GENDER)
OR (GENDER=@GENDER AND AGE=@AGE)
OR (CITY=@CITY AND AGE=@AGE)
OR (CITY=@CITY)
OR (GENDER=@GENDER)
OR (AGE=@AGE)
)
Is there an even shorter more efficient way to do this?
有没有更短更有效的方法来做到这一点？
If yes, it is preferable for the method to be compatible with JOIN's also.
如果是，则该方法最好也与 JOIN 兼容。
回答by cjk
Alternatively to the ISNULL/ COALESCEoptions, you can test the parameters for being null:
除了ISNULL/COALESCE选项，您还可以测试参数是否为空：
SELECT NAME
FROM TABLE
WHERE
(@City IS NULL OR City = @City)
AND
(@Gender IS NULL OR Gender = @Gender)
AND
(@Age IS NULL OR Age = @Age)
回答by silly
what about this?
那这个呢？
SELECT
NAME
FROM TABLE
WHERE CITY = COALESCE(@CITY, CITY)
AND GENDER = COALESCE(@GENDER, GENDER)
AND AGE = COALESCE(@AGE, AGE)
回答by Greg
Try something like this:
尝试这样的事情：
SELECT NAME
FROM TABLE
WHERE
City = IsNull(@City, City) AND
Gender = IsNull(@Gender, Gender) AND
Age = IsNull(@Age, Age)
OR:
或者：
SELECT NAME
FROM TABLE
WHERE
(City = @City OR @City IS NULL) AND
(Gender = @Gender OR @Gender IS NULL) AND
(Age = @Age OR @Age IS NULL)
回答by userGS
SELECT NAME
FROM TABLE
WHERE
City = case when isnull(@City ,'') = '' then City
else @City end
AND
Gender = case when isnull(@Gender ,'') = '' then Gender
else @Gender end
AND
Age = case when isnull(@Age ,0) = 0 then Age
else @Age end
回答by John Dewey
Possibly this:
可能是这个：
create procedure myProc
--Params
@CITY VARCHAR(100) = NULL,
@GENDER VARCHAR(100) = NULL,
@AGE VARCHAR(100) = NULL
as
SELECT NAME FROM [TABLE]
WHERE ISNULL(CITY,'')=ISNULL(@CITY,ISNULL(CITY,''))
AND ISNULL(GENDER,'')=ISNULL(@GENDER,ISNULL(GENDER,''))
AND ISNULL(AGE,'')=ISNULL(@AGE,ISNULL(AGE,''))
go
Assuming the columns in the WHERE clause are nullable, using ISNULL to avoid null comparison.
假设 WHERE 子句中的列可以为空，使用 ISNULL 来避免空值比较。
