存储过程-发送一个参数列表，以便与in子句(副本)一起使用

[DUPLICATE of Parameterize an SQL IN clause

[参数化子句中的SQL的重复

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

问题:我需要向存储过程中发送一个值列表，以便在WHERE子句中使用in语句。

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.

从上面我得到0个结果，但是我想得到i或M类型的结果。

2 个解决方案

#1

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

我不相信IN子句可以处理表达式，我相信您需要一个文字。

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)

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

我在过去也遇到过这个问题，并在网上找到了一些解决问题的建议。我发现的解决方案是从输入字符串中解析单个值，将它们作为行放到临时表中，然后使用带有IN子句的子select来获取值。下面是我正在使用的代码的简化。它假设IN子句中的值是smallint，使用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)

#1