CODEWITHSUNDEEP
sql-serverswitch-statementcase
Vikrant
Vikrant

Reputation: 147

SQL CASE statement with table and operator

trying to apply operator on the basis on condition.

I want to make below query work;

SELECT fName,
       LName
WHERE active =1
  AND CASE @statusid
           WHEN -100 THEN (StatusID NOT IN (-5,-3))
           WHEN -101 THEN StatusID NOT IN (-5, -3, -1)
           WHEN -102 THEN StatusID NOT IN (-3)
           ELSE StatusID = @statusid
      END

Upvotes: 0

Views: 976

Answers (1)

Thom A
Thom A

Reputation: 95906

As I mention in my comment, T-SQL does not support Case (Switch) statements, only CASE expressions. What you need to do here, however, is just use plain "old" boolean logic:

SELECT fName,
       lName
--Where is your FROM?
--I have added one
FROM dbo.YourTable
WHERE active = 1
  AND ((@statusid = -100 AND StatusID NOT IN (-5,-3))
   OR  (@statusid = -101 AND StatusID NOT IN (-5,-3,-1))
   OR  (@statusid = -102 AND StatusID != -3)
   OR  StatusID = @Statusid);

Upvotes: 3

Related Questions