Stored procedure - Send a list of parameters in for use with the IN clause

Question

[DUPLICATE of http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause]

---

Issue: I need to send in a list of values into a stored procedure for use with an IN statement in the WHERE clause.

Example:

declare @SPType NVARCHAR ( 30 )
declare @SPCodeA int

set @SPCodeA  = 75
set @SPType  = '''I'',''M'''

select * from table 
where CodeA=@SPCodeA 
and Type in (@SPType)

From the above i am getting 0 results but i would like to get results where Type is I or M.

Answer 1

I don't believe that the 'IN' clause can handle an expression, I believe you need a literal.

Try:

declare @sql NVARCHAR(MAX)

declare @SPType NVARCHAR ( 30 )
declare @SPCodeA int

set @SPCodeA  = 75
set @SPType  = '''I'',''M'''
SET @sql = 'select * from table where CodeA=' + CONVERT(NVARCHAR(MAX), @spcodea) +' and Type in (' + @sptype + ')'

EXEC (@sql)

Answer 2

I've run into this problem also in the past, and found some advice on the web to solve it. The solution I found was to parse the individual values from the input string, place them as rows into a temporary table, then use a subselect with the IN clause to get the values. Below is a simplification of the code I'm using. It assumes the values from the IN clause are smallints, it would be relaitvely easy to change to use a varchar.

For further reference, see this article with different ways to solve this issue.

DECLARE @TempList table
(
    Enum smallint
)

DECLARE @Enum varchar(10), @Pos int

SET @ValueList = LTRIM(RTRIM(@ValueList))+ ','
SET @Pos = CHARINDEX(',', @ValueList, 1)

IF REPLACE(@ValueList, ',', '')  ''
BEGIN
    WHILE @Pos > 0
    BEGIN
     SET @Enum = LTRIM(RTRIM(LEFT(@ValueList, @Pos - 1)))
     IF @Enum  ''
     BEGIN
      INSERT INTO @TempList (Enum) VALUES (CAST(@Enum AS smallint)) --Use Appropriate conversion
     END
     SET @ValueList = RIGHT(@ValueList, LEN(@ValueList) - @Pos)
     SET @Pos = CHARINDEX(',', @ValueList, 1)
    END
END 

SELECT *
FROM ExampleTable
WHERE
ExampleTable.Enum in (select Enum from @TempList)