SQL Server - 带有声明变量的 In 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2944511/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server - In clause with a declared variable
提问by Melursus
Let say I got the following :
假设我得到以下信息：
DECLARE @ExcludedList VARCHAR(MAX)
SET @ExcludedList = 3 + ', ' + 4 + ' ,' + '22'
SELECT * FROM A WHERE Id NOT IN (@ExcludedList)
Error : Conversion failed when converting the varchar value ', ' to data type int.
错误：将 varchar 值“,”转换为数据类型 int 时转换失败。
I understand why the error is there but I don't know how to solve it...
我明白为什么会出现错误，但我不知道如何解决它...
采纳答案by TonyP
You need to execute this as a dynamic sp like
您需要将其作为动态 sp 执行
DECLARE @ExcludedList VARCHAR(MAX)
SET @ExcludedList = '3,4,22,6014'
declare @sql nvarchar(Max)
Set @sql='SELECT * FROM [A] WHERE Id NOT IN ('+@ExcludedList+')'
exec sp_executesql @sql
回答by Mikkel Tronsrud
This is an example where I use the table variable to list multiple values in an IN clause. The obvious reason is to be able to change the list of values only one place in a long procedure.
To make it even more dynamic and alowing user input, I suggest declaring a varchar variable for the input, and then using a WHILE to loop trough the data in the variable and insert it into the table variable.
这是我使用表变量在 IN 子句中列出多个值的示例。显而易见的原因是在一个漫长的过程中只能在一个地方更改值列表。
为了使其更加动态并允许用户输入，我建议为输入声明一个 varchar 变量，然后使用 WHILE 循环遍历变量中的数据并将其插入到表变量中。
Replace @your_list, Your_table and the values with real stuff.
用真实的东西替换@your_list、Your_table 和值。
DECLARE @your_list TABLE (list varchar(25))
INSERT into @your_list
VALUES ('value1'),('value2376')
SELECT *
FROM your_table
WHERE your_column in ( select list from @your_list )
The select statement abowe will do the same as:
上面的 select 语句将执行相同的操作：
SELECT *
FROM your_table
WHERE your_column in ('value','value2376' )
回答by Carl Osterman
DECLARE @IDQuery VARCHAR(MAX)
SET @IDQuery = 'SELECT ID FROM SomeTable WHERE Condition=Something'
DECLARE @ExcludedList TABLE(ID VARCHAR(MAX))
INSERT INTO @ExcludedList EXEC(@IDQuery)
SELECT * FROM A WHERE Id NOT IN (@ExcludedList)
I know I'm responding to an old post but I wanted to share an example of how to use Variable Tables when one wants to avoid using dynamic SQL. I'm not sure if its the most efficient way, however this has worked in the past for me when dynamic SQL was not an option.
我知道我正在回复一篇旧帖子，但我想分享一个示例，说明当人们想要避免使用动态 SQL 时如何使用变量表。我不确定它是否是最有效的方法，但是当动态 SQL 不是一种选择时，这在过去对我有用。
回答by OMG Ponies
You can't use a variable in an INclause - you need to use dynamic SQL, or use a function (TSQL or CLR) to convert the list of values into a table.
您不能在IN子句中使用变量- 您需要使用动态 SQL或使用函数（TSQL 或 CLR）将值列表转换为表。
Dynamic SQL example:
动态 SQL 示例：
DECLARE @ExcludedList VARCHAR(MAX)
SET @ExcludedList = 3 + ',' + 4 + ',' + '22'
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT * FROM A WHERE Id NOT IN (@ExcludedList) '
BEGIN
EXEC sp_executesql @SQL '@ExcludedList VARCHAR(MAX)' @ExcludedList
END
回答by laughsloudly
First, create a quick function that will split a delimited list of values into a table, like this:
首先，创建一个快速函数，它将分隔的值列表拆分为一个表，如下所示：
CREATE FUNCTION dbo.udf_SplitVariable
(
@List varchar(8000),
@SplitOn varchar(5) = ','
)
RETURNS @RtnValue TABLE
(
Id INT IDENTITY(1,1),
Value VARCHAR(8000)
)
AS
BEGIN
--Account for ticks
SET @List = (REPLACE(@List, '''', ''))
--Account for 'emptynull'
IF LTRIM(RTRIM(@List)) = 'emptynull'
BEGIN
SET @List = ''
END
--Loop through all of the items in the string and add records for each item
WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN
INSERT INTO @RtnValue (value)
SELECT Value = LTRIM(RTRIM(SUBSTRING(@List, 1, CHARINDEX(@SplitOn, @List)-1)))
SET @List = SUBSTRING(@List, CHARINDEX(@SplitOn,@List) + LEN(@SplitOn), LEN(@List))
END
INSERT INTO @RtnValue (Value)
SELECT Value = LTRIM(RTRIM(@List))
RETURN
END
Then call the function like this...
然后像这样调用函数...
SELECT *
FROM A
LEFT OUTER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value
WHERE f.Id IS NULL
This has worked really well on our project...
这对我们的项目非常有效......
Of course, the opposite could also be done, if that was the case (though not your question).
当然，如果是这样的话，也可以做相反的事情（尽管不是你的问题）。
SELECT *
FROM A
INNER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value
And this really comes in handy when dealing with reports that have an optional multi-select parameter list. If the parameter is NULL you want all values selected, but if it has one or more values you want the report data filtered on those values. Then use SQL like this:
在处理具有可选多选参数列表的报告时，这确实派上用场。如果参数为 NULL，您希望选择所有值，但如果它有一个或多个值，您希望根据这些值过滤报告数据。然后像这样使用SQL：
SELECT *
FROM A
INNER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value OR @ExcludeList IS NULL
This way, if @ExcludeList is a NULL value, the OR clause in the join becomes a switch that turns off filtering on this value. Very handy...
这样，如果@ExcludeList 是 NULL 值，则连接中的 OR 子句成为关闭对该值过滤的开关。非常便利...
回答by user1059637
I have another solution to do it without dynamic query. We can do it with the help of xquery as well.
我有另一个解决方案可以在没有动态查询的情况下完成。我们也可以在 xquery 的帮助下做到这一点。
SET @Xml = cast(('<A>'+replace('3,4,22,6014',',' ,'</A><A>')+'</A>') AS XML)
Select @Xml
SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)
Here is the complete solution : http://raresql.com/2011/12/21/how-to-use-multiple-values-for-in-clause-using-same-parameter-sql-server/
这是完整的解决方案：http: //raresql.com/2011/12/21/how-to-use-multiple-values-for-in-clause-using-same-parameter-sql-server/
回答by Salil
I think problem is in
我认为问题出在
3 + ', ' + 4
change it to
将其更改为
'3' + ', ' + '4'
DECLARE @ExcludedList VARCHAR(MAX)
SET @ExcludedList = '3' + ', ' + '4' + ' ,' + '22'
SELECT * FROM A WHERE Id NOT IN (@ExcludedList)
SET @ExcludedListe such that your query should become
SET @ExcludedListe 这样你的查询应该变成
either
任何一个
SELECT * FROM A WHERE Id NOT IN ('3', '4', '22')
or
或者
SELECT * FROM A WHERE Id NOT IN (3, 4, 22)
回答by Makis
Try this:
尝试这个：
CREATE PROCEDURE MyProc @excludedlist integer_list_tbltype READONLY AS
SELECT * FROM A WHERE ID NOT IN (@excludedlist)
And then call it like this:
然后像这样调用它：
DECLARE @ExcludedList integer_list_tbltype
INSERT @ExcludedList(n) VALUES(3, 4, 22)
exec MyProc @ExcludedList
