SQL server conditional case statement in where clause using IN clause
Posted on 2014-07-23
I have a situation where I need to filter based on a variable that is being set on a webpage, and if the variable isn't set, then I have to filter based on a comma-delimited string to see if there's a match.
Here it is:
First, I build the comma delimited string, with a STUFF in the FROM clause of the select statement - as follows:
', ' + dx.class
Join data_Xref dx ON sr.Text = dx.text
WHERE sr.userid = @vUID
', ' + dx.class
for xml path ('')
) mg (Classes)
mg.Classes now contains a long string, comma delimited, of the various classes for this particular user (@Vuid)
Now I want to add an additional condition to the WHERE clause I want to do the filtering.
If a variable with a class name was selected on the input screen, I only want to include that particular class.
If the variable from the input screen is NULL or '', then I want to include all classes from mg.Classes, and I want to filter out the classes that are not part of that string.
I tried many things in the WHERE clause, and they aren't working.
Even though the value of string is correct (it's a comma-delimited string of classes), the WHERE clause does not like the "IN" and is returning an error (Incorrect syntax near the keyword 'CASE'.)
How can I filter to see if the class in the table is in the list of allowed classes that was returned by the STUFF.
Here's a sample:
WHERE dx.CLASS IN
when @vClass is NULL
then '(' + mg.Classes + ')'
when @vClass = '' then '(' + mg.Classes + ')'
ELSE @vClass END )
This is complicated, but any help would be GREATLY appreciated!