CASE in WHERE statement

[thamms]

Hi,

I am wondering if this is possible:

SELECT TOP 20 *,
CASE @Mode = 'Equity' THEN
Code IN ('ABC', 'CDE')
CASE @Mode = 'NonEquity' THEN
Code NOT IN ('ABC', 'CDE')
FROM #myTable
ORDER BY ABS(Ratio*100-100) DESC

Error:
Incorrect syntax near the keyword 'IN'.

Basically, I need the "WHERE clause" to change depending on the variable value. But I'm not sure where to stick the WHERE clause.

Most of the examples online are too simplistic.

Thanks

 

[thamms]

Oops my syntax is not right, even for this messed up example:

SELECT TOP 20 *,
CASE
WHEN @Mode = 'Equity'
THEN StCode IN('ABC', 'CDE')
WHEN @Mode = 'NonEquity'
THEN StCode IN ('ABC', 'CDE')
FROM #myTable
ORDER BY abs(Ratio*100-100) DESC

 

[gmmastros]

I don't think you need to use Case/When. Simple boolean logic should do it.

Select Top 20 *
From   #myTable
Where  (@Mode = 'Equity' And Code In ('ABC','CDE'))
       Or (@Mode = 'NonEquity' And Code Not In ('ABC','CDE')
ORDER BY ABS(Ratio*100-100) DESC

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Of course, I missed a closing parenthesis.

Select Top 20 *
From   #myTable
Where  (@Mode = 'Equity' And Code In ('ABC','CDE'))
       Or (@Mode = 'NonEquity' And Code Not In ('ABC','CDE')[!])[/!]
ORDER BY ABS(Ratio*100-100) DESC

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[thamms]

crud, you are right