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

[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.

From stackoverflow

• 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)
  

  Ioxp : I was thinking of doing it this way but instead of using EXEC i was going to use EXECUTE @ErrorCode = sp_ExecuteSQL @sql so that i can get an error response to know if i need to rollback the code i'm executing.

  hunterjrj : Loxp, good thinking though there is nothing to rollback with a simple SELECT...

  Ioxp : Good point. there are updates and inserts as well as selects that are based off the same parameter list.

• 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)