Set variable with multiple values and use IN [duplicate]

Question

Possible Duplicate:
Parameterizing a SQL IN clause?

In SQL Server I'd like to do something to this effect...

DECLARE @Values varchar(1000)  SET @Values = 'A, B, C'  SELECT   blah FROM    foo WHERE   myField IN (@Values) 

Is this possible or how to accomplish this?

Answer 1

You need a table variable:

declare @values table (     Value varchar(1000) )  insert into @values values ('A') insert into @values values ('B') insert into @values values ('C')  select blah from foo where myField in (select value from @values) 

Answer 2

Ideally you shouldn't be splitting strings in T-SQL at all.

Barring that change, on older versions before SQL Server 2016, create a split function:

CREATE FUNCTION dbo.SplitStrings (     @List      nvarchar(max),      @Delimiter nvarchar(2) ) RETURNS TABLE WITH SCHEMABINDING AS   RETURN ( WITH x(x) AS     (       SELECT CONVERT(xml, N'<root><i>'          + REPLACE(@List, @Delimiter, N'</i><i>')          + N'</i></root>')     )     SELECT Item = LTRIM(RTRIM(i.i.value(N'.',N'nvarchar(max)')))       FROM x CROSS APPLY x.nodes(N'//root/i') AS i(i)   ); GO 

Now you can say:

DECLARE @Values varchar(1000);  SET @Values = 'A, B, C';  SELECT blah   FROM dbo.foo   INNER JOIN dbo.SplitStrings(@Values, ',') AS s     ON s.Item = foo.myField; 

On SQL Server 2016 or above (or Azure SQL Database), it is much simpler and more efficient, however you do have to manually apply LTRIM() to take away any leading spaces:

DECLARE @Values varchar(1000) = 'A, B, C';  SELECT blah   FROM dbo.foo   INNER JOIN STRING_SPLIT(@Values, ',') AS s     ON LTRIM(s.value) = foo.myField;