Categories
sql sql-server sql-server-2008

Optional Arguments in WHERE Clause

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 (
([email protected] AND [email protected] AND [email protected])
OR ([email protected] AND [email protected])
OR ([email protected] AND [email protected])
OR ([email protected] AND [email protected])
OR ([email protected])
OR ([email protected])
OR ([email protected])
)

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.

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)