CODEWITHSUNDEEP
sqlsql-servert-sql
Dewinky
Dewinky

Reputation: 83

SQL CASE IN() statement

I have 3 parameters which i would like to make into a where clause under a certain condition.

I am trying to write the following; When the sum of @p1, @p2 and @p3 equals -3. I want to return all the rows;

When the sum is any other value, I want to return the rows that fall in the IN(@p1, @p2, @p3)

I have tried the below but keep seeing syntax errors and nothing I searched comes close to what I am trying to do.

Any help would be appreciated:

@p1 INT = -1
@p2 INT = -1,
@p3 INT = -1

SELECT *
FROM table1
WHERE
(
CASE WHEN @p1 + P2 + @p3 = -3 THEN True ELSE field1 END
IN( CASE WHEN @p1 + P2 + @p3 = -3 THEN True ELSE @p1. @p2, P3 END )
)

Upvotes: 1

Views: 151

Answers (5)

Alex Zen
Alex Zen

Reputation: 926

As others already pointed out, there's no need to use a case expression here, and you would have a better solution just using a proper where expression.

Case expressions are just a way to return a value based on one or more conditions. You can actually use them on the where clause, you just need to manage the value returned.

Just for fun:

WHERE CASE WHEN @p1 + @p2 + @p3 = -3
           THEN 0
           ELSE CASE WHEN myField IN (@p1, @p2, @p3)
                     THEN 0
                     ELSE 1 END END = 0

Upvotes: 2

paparazzo
paparazzo

Reputation: 45096

case is not convenient in the where 

declare @p1 INT = -1, @p2 INT = 2, @p3 INT = -4, @s int = -3;
select @p1, @p2, @p3, @s, @p1 + @p2 + @p3;
declare @tf bit  = (select iif(@p1 + @p2 + @p3 = @s, 1, 0));
declare @T table (pk int identity primary key, fk int);
insert into @T (fk) values (-1), (5), (6)

SELECT *
FROM @T t
WHERE @p1 + @p2 + @p3 = @s 
   OR t.fk in (@p1, @p2, @p3)

SELECT *
FROM @T t
WHERE @tf = 1
   OR t.fk in (@p1, @p2, @p3)

set @p2 = 5;

SELECT *
FROM @T t
WHERE @p1 + @p2 + @p3 = @s 
   OR t.fk in (@p1, @p2, @p3)

pk          fk
----------- -----------
1           -1
2           5
3           6

pk          fk
----------- -----------
1           -1
2           5
3           6

pk          fk
----------- -----------
1           -1
2           5

Upvotes: 0

Jason Goemaat
Jason Goemaat

Reputation: 29214

IF (@p1 + @p2 + @p3) = -3 THEN
  SELECT * FROM table1
ELSE
  SELECT * FROM table1
  WHERE field1 IN (@p1, @p2, @p3)

Upvotes: 0

Yahya Mukhtar
Yahya Mukhtar

Reputation: 474

@p1 INT = -1
@p2 INT = -1,
@p3 INT = -1

SELECT *
FROM table1
WHERE (@p1 + P2 + @p3 = -3) OR ((@p1 + P2 + @p3 <> -3) AND field1 in (@p1, @p2, P3))

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

In SQL CASE is an expression that returns a single scalar value. It cannot be used to control execution flow like in procedural languages.

You can try this instead:

WHERE (@p1 + P2 + @p3 = -3) 
      OR
      (myField IN (@p1, @p2, @p3))

Upvotes: 4

Related Questions