WHERE 子句中的可选参数 [英] Optional Arguments in WHERE Clause

问题描述

假设有一个有 3 个参数的存储过程.在所有的可能性中，我希望通过一个 WHERE 子句来实现这一点，而不会因过多地使用 () AND () OR () 而失去控制...

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 WHERE clause without getting out of control with using () AND () OR () too much...

示例:

    //Params
@CITY VARCHAR(100) = NULL,
@GENDER VARCHAR(100) = NULL,
@AGE VARCHAR(100) = NULL

我想你可以对每个变量使用 IF BEGIN ... END 如果存在，但这会使代码比预期的要长很多..

I suppose you can do it using IF BEGIN ... END for each Variable if Exists, but that makes the code alot longer than desired..

下面的这个方法行不通，因为它的方法太长了(大约有 10 个这样的不同字段，但示例只有 3 个.)而且我不确定它是否甚至直接提取了独特的值......

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

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?

如果是，则该方法最好也与 JOIN 兼容.

If yes, it is preferable for the method to be compatible with JOIN's also.

推荐答案

替代 ISNULL/COALESCE 选项，您可以测试参数是否为空:

Alternatively to the ISNULL / COALESCE options, you can test the parameters for being null:

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) 

这篇关于WHERE 子句中的可选参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！