Optional Arguments in WHERE Clause [duplicate]

Question

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.

Answer 1

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)  

Answer 2

what about this?

SELECT     NAME FROM TABLE  WHERE CITY = COALESCE(@CITY, CITY)     AND GENDER = COALESCE(@GENDER, GENDER)     AND AGE = COALESCE(@AGE, AGE)