CODEWITHSUNDEEP
sqlsql-server
Manolel
Manolel

Reputation: 61

CASE and IN in WHERE

I have an issue with my SQL Server request: 

SELECT *
FROM table 
WHERE M.StatutMandatId = 

CASE
    WHEN @Mandat = '1' THEN '5'
    WHEN @Mandat = '0' THEN 'IN(1,2,3,4,5)'
END

But I have this issue:

Conversion failed when converting the varchar value 'IN(1,2,3)' to data type int.

It's not possible to use IN like that?

Upvotes: 0

Views: 79

Answers (3)

Novice Programmer
Novice Programmer

Reputation: 423

if you really want to use caseand IN inside where following would work (need to use temporary table).

DECLARE @StatutMandatIds table (meetingstatus int)

insert into @StatutMandatIds select 1
insert into @StatutMandatIds select 2
insert into @StatutMandatIds select 3
insert into @StatutMandatIds select 4
insert into @StatutMandatIds select 5

SELECT *
FROM table 
WHERE M.StatutMandatId IN 
(CASE
    WHEN @Mandat = '1' THEN 5
    WHEN @Mandat = '0' THEN (select * from @StatutMandatIds)
END)

OR you can do simplify like this

SELECT *
FROM table 
WHERE (@Mandat= '1' AND M.StatutMandatId = 5) OR (@Mandat = '0' AND M.StatutMandatId IN (1,2,3,4,5))

Upvotes: 0

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391346

You can't do an IN like that, instead if you have to use the CASE do it like this:

SELECT *
FROM table 
WHERE
CASE
    WHEN @Mandat = '1' AND M.StatutMandatId = 5 THEN 1
    WHEN @Mandat = '0' AND M.StatutMandatId IN(1,2,3,4,5) THEN 1
    ELSE 0
END = 1

It probably won't be very performant, I would instead restructure it like this:

SELECT *
FROM table 
WHERE
    (@Mandat = '1' AND M.StatutMandatId = 5)
 OR (@Mandat = '0' AND M.StatutMandatId IN(1,2,3,4,5))

Upvotes: 4

Alex
Alex

Reputation: 21766

No, you can't create dynamic SQL like that. You could achieve the same using the follow statement though

SELECT *
FROM table 
WHERE (M.StatutMandatId IN(1,2,3,4,5) AND @Mandat=0)
OR (M.StatutMandatId =5 AND @Mandat=1)

Upvotes: 5

Related Questions