Dynamic In Claus with Case

[Moebius01]

Been digging around, but can't get this to work, and I'm not sure if it's possible this way. I'm trying to add a Case to the IN Clause. The problem is in this case it needs to be a list. I may be missing something obvious here, but there's the basic idea:

DECLARE @Var INT
SET @Var = 1
SELECT *
FROM [TABLE]
WHERE [FIELD] IN (CASE @Var
    WHEN 1 THEN ('1,2,3')
    WHEN 2 THEN ('4,5,6')
    END)

When using that approach I get a syntax error converting '1,2,3' to INT (the field is an INT). If I don't enclose the list in quotes the CASE generates a syntax error near ,.

The only other approach I can think of at the moment (and one I'd like to avoid due to complexity of other sections) is to build the IN statement as a variable, then build the entire statement into a variable to exec.

 

[LNBruno]

Looks like you're going to have to use dynamic SQL with your dynamic clause to get what you're after:

create table #table
   (iField int)

insert into #table (iField) values (1)
insert into #table (iField) values (2)
insert into #table (iField) values (3)
insert into #table (iField) values (4)
insert into #table (iField) values (5)
insert into #table (iField) values (6)

DECLARE @Var INT
DECLARE @SQL nvarchar(500)
SET @Var = 1

SET @SQL = 'SELECT *
FROM #table
WHERE iField IN (' + (CASE @Var
    WHEN 1 THEN ('1,2,3')
    WHEN 2 THEN ('4,5,6')
    END) + ')'

print @SQL

EXECUTE  sp_executeSQL @SQL

< M!ke >
[small]"Oops" is never a good thing.[/small]

 

[Gixonita]

Hi

You could try

DECLARE @Var INT
SET @Var = 2

SELECT *

FROM [table]

WHERE	(
	 [field] IN (1,2,3) AND
	 @Var = 1
	) OR
	(
	 [field] IN (4,5,6) AND
	 @Var = 2
	)

Hope this helps

 

[Moebius01]

Ended up going with Gixonita's suggestion, thanks for the idea. I needed a spark.

 

[Gixonita]

Glad I could help