Optional Arguments in WHERE Clause
sql, sql-server, sql-server-2008
Solution
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)
Problem
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... Example: ``` //Params @CITY VARCHAR(100) = NULL, @GENDER VARCHAR(100) = NULL, @AGE VARCHAR(100) = NULL ``` I suppose you can do it using `IF BEGIN ... END` for each Variable if Exists, but that makes the code alot longer than desired.. 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? If yes, it is preferable for the method to be compatible with JOIN's also.