CODEWITHSUNDEEP
sql-servert-sql
Piotr X
Piotr X

Reputation: 127

Expression after THEN in 'CASE ... WHEN ... THEN' syntax

This sql is working properly:

DECLARE @check bit = 1

  SELECT * FROM [TABLE]
  WHERE [STATUS] = 1
  AND [TYPE] =
  CASE WHEN @check = 0 THEN 'typeA'
  ELSE 'typeB'
  END

But when I I am trying to use expression with 'OR' I am getting error:

DECLARE @check bit = 1

  SELECT * FROM [TABLE]
  WHERE [STATUS] = 1
  AND [TYPE] =
  CASE WHEN @check = 0 THEN 'typeA'
  ELSE ('typeB' OR 'typeC')
  END

For example, I expect to have:

If(@check = 0) -> ... WHERE [Type] = sth OR sth

Upvotes: 1

Views: 1202

Answers (2)

Vqf5mG96cSTT
Vqf5mG96cSTT

Reputation: 2891

A switch case doesn't work like that. The ELSE clause is basically what it should default to if none of the other filters provide a solution. A CASE is nothing more than a series of IF statements. So you've basically written the following:

if the value of @check is 0 then return 'typeA'
otherwise return ('typeB' OR 'typeC')

But how can SQL return multiple values? Most likely you should change your CASE statement like below. Instead of checking on @check you should check on the element that has multiple options. The query below will return the records where TYPE is either typeA or typeB when @check is 0.

  DECLARE @check bit = 1

  SELECT * FROM @table
  WHERE [STATUS] = 1
  AND CASE [TYPE] WHEN 'typeA' THEN 0 WHEN 'typeB' THEN 0 WHEN 'typeC' THEN 1 END = 0 AND @check = 0

Upvotes: 1

Junc Mayl
Junc Mayl

Reputation: 101

is this what you're trying to accomplish? (expanded for readability)

SELECT  *
FROM    [TABLE]
WHERE   [STATUS] = 1
        AND 
        (
            (
                @check = 0
                AND [TYPE] = 'typeA'
            )
            OR
            (
                @check = 1
                AND [TYPE] IN ('typeB', 'typeC')
            )
        )

Upvotes: 3

Related Questions