SQL server conditional case statement in where clause using IN clause

sql server case statement in where clause

MS SQL Server Question

Hi,

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.

', ' + 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

( CASE

when @vClass is NULL

then '(' + mg.Classes + ')'

when @vClass = '' then '(' + mg.Classes + ')'

Source: www.experts-exchange.com

Category: Forex

Similar articles: