Set variable with multiple values and use IN

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

User · Accepted Answer

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

User · Answer

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  lt root gt  lt i gt              REPLACE  List   Delimiter  N  lt  i gt  lt i gt               N  lt  i gt  lt  root gt              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

User · Answer

Use a Temp Table or a Table variable  e g   select  A  as  value  into  tmp union select  B  union  select  C    and then  SELECT    blah  FROM    foo  WHERE   myField IN  select  value  from  tmp     or   SELECT    f blah  FROM foo f INNER JOIN  tmp t ON f myField   t  value

[sql-server] Set variable with multiple values and use IN

Examples related to sql-server

Examples related to tsql