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

Question

[DUPLICATE of Parameterize an 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.

score 3 · Accepted Answer · answered Feb 10 '09 at 18:27

3

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)

answered Feb 10 '09 at 18:27

hunterjrj

774
1
5
16

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. – Random Developer Feb 10 '09 at 18:34
Loxp, good thinking though there is nothing to rollback with a simple SELECT... – hunterjrj Feb 10 '09 at 18:35
Good point. there are updates and inserts as well as selects that are based off the same parameter list. – Random Developer Feb 10 '09 at 19:02

Steve Wranovsky · Answer 2 · 2009-02-10T18:59:19.057

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)

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

2 Answers2