CODEWITHSUNDEEP
sqlsql-server
Eric Pruitt
Eric Pruitt

Reputation: 3

SQL Server : how to use a variable with multiple variables in a case statement in a where clause

I am attempting to build a simple lookup table that will have its own web page display. I am providing simple search variables, and one of those variables is Status. I am looking for them to be able to Choose from Active, Inactive, or both, I am looking to easily look for both at once. There are other status in the database such as 'D' for a soft delete that I do not want returned at all.

Declare @stat nvarchar(5) = 3

Select [Status] 
from tableUser
where [Status] in (CASE @stat
                      WHEN 1 THEN 'A'
                      WHEN 2 THEN 'I'
                      WHEN 3 THEN 'A','I'
                   END)

The above is what I have tried.

Upvotes: 0

Views: 58

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415630

You want the CASE statement to produce an expression, but that's not how it works. CASE statements produce values (which can be included as expressions, but the expressions must still reduce to values at query compile time). 'A','I' does not reduce to a value, so you cannot use it as the result of a CASE statement.

Instead, write the condition more like this:

WHERE 1 = CASE WHEN @stat = 1 AND [Status] = 'A' THEN 1
               WHEN @stat = 2 AND [Status] = 'I' THEN 1
               WHEN @stat = 3 AND [Status] IN ('A', 'I') THEN 1
               ELSE 0 END

or remove the CASE expressions and build all that directly into the WHERE clause:

WHERE (    (@stat = 1 AND [Status] = 'A') 
        OR (@stat = 2 AND [Status] = 'I')
        OR (@stat = 3 AND [Status] IN ('A', 'I')) 
      )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269523

Just use boolean logic:

WHERE (@stat IN (1, 3) AND Status = 'A') OR
      (@stat IN (2, 3) AND Status = 'I')

Upvotes: 2

Related Questions