CODEWITHSUNDEEP
sqlsql-server
Encryption
Encryption

Reputation: 1897

Sql Server IN Clause Issue

Writing a stored procedure that will have multiple input parameters. The parameters may not always have values and could be empty. But since the possibility exists that each parameter may contain values I have to include the criterion that utilizing those parameters in the query.

My query looks something like this:

SELECT DISTINCT COUNT(*) AS SRM
FROM table p
WHERE p.gender IN (SELECT * FROM Fn_SplitParms(@gender)) AND 
p.ethnicity IN (SELECT * FROM Fn_SplitParms(@race)) AND 
p.marital_status IN (SELECT * FROM Fn_SplitParms(@maritalstatus))

So my problem is if @gender is empty(' ') the query will return data where gender field is empty when I really want to just ignore p.gender all together. I don't want to have to accomplish this task using IF/ELSE conditional statements because they would be too numerous.

Is there any way to use CASE with IN for this scenario? OR Is there other logic that I'm just not comprehending that will solve this?

Having trouble finding something that works well...

Thanks!

Upvotes: 1

Views: 452

Answers (2)

Cade Roux
Cade Roux

Reputation: 89661

You might also want to consider table-valued parameters (if using SQL Server 2008 and up) - these can sometimes make the code simpler, since they are treated as tables (which in your case, may be empty) and you can join - plus no awkward split function required.

Upvotes: 0

Eric
Eric

Reputation: 95133

Use or:

SELECT DISTINCT COUNT(*) AS SRM
FROM table p
WHERE 
(p.gender IN (SELECT * FROM Fn_SplitParms(@gender)) OR @gender = '')
AND (p.ethnicity IN (SELECT * FROM Fn_SplitParms(@race)) OR @race = '')
AND (p.marital_status IN (SELECT * FROM Fn_SplitParms(@maritalstatus)) OR @maritalstatus = '')

Upvotes: 2

Related Questions