SQL Server - 带有声明变量的 In 子句

发布于 2024-09-04 04:17:22 字数 256 浏览 9 评论 0原文

假设我得到以下信息：

DECLARE @ExcludedList VARCHAR(MAX)

SET @ExcludedList = 3 + ', ' + 4 + ' ,' + '22'

SELECT * FROM A WHERE Id NOT IN (@ExcludedList)

错误：将 varchar 值“,”转换为数据类型 int 时转换失败。

我明白为什么会出现错误，但我不知道如何解决它......

原文

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.

I understand why the error is there but I don't know how to solve it...

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

野心澎湃 2024-09-11 04:17:22

这是我使用表变量列出多个的示例
IN 子句中的值。显而易见的原因是能够改变
值列表仅在长过程中的一个位置。
为了使其更加动态并允许用户输入，我建议
为输入声明一个 varchar 变量，然后使用 WHILE 来
循环遍历变量中的数据并将其插入表中
变量。

将 @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 )

上面的 select 语句将执行与以下相同的操作：

SELECT *  
FROM your_table 
WHERE your_column in ('value','value2376' )

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.

Replace @your_list, Your_table and the values with real stuff.

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 *  
FROM your_table 
WHERE your_column in ('value','value2376' )

回复收藏 0 原文

你的往事 2024-09-11 04:17:22

您需要将其作为动态 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

You need to execute this as a dynamic sp like

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

回复收藏 0 原文

离旧人 2024-09-11 04:17:22

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)

我知道我正在回复一篇旧帖子，但我想分享一个当人们想避免使用动态 SQL 时如何使用变量表的示例。我不确定这是否是最有效的方法，但是过去当动态 SQL 不是一个选项时，这对我有用。

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.

回复收藏 0 原文

高冷爸爸 2024-09-11 04:17:22

您不能在 IN 子句中使用变量 - 您需要使用动态 SQL ，或使用函数（TSQL 或 CLR）将值列表转换为一个表。

动态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

You can't use a variable in an IN clause - you need to use dynamic SQL, or use a function (TSQL or CLR) to convert the list of values into a table.

Dynamic SQL example:

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

回复收藏 0 原文

看海 2024-09-11 04:17:22

首先，创建一个快速函数，将分隔的值列表拆分到一个表中，如下所示：

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

然后像这样调用该函数...

SELECT * 
FROM A
LEFT OUTER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value
WHERE f.Id IS NULL

这在我们的项目中效果非常好...

当然，也可以执行相反的操作，如果是这样的话（虽然不是你的问题）。

SELECT * 
FROM A
INNER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value

在处理具有可选多选参数列表的报表时，这确实非常有用。如果该参数为 NULL，您希望选择所有值，但如果它有一个或多个值，您希望根据这些值过滤报告数据。然后像这样使用 SQL：

SELECT * 
FROM A
INNER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value OR @ExcludeList IS NULL

这样，如果 @ExcludeList 是 NULL 值，则连接中的 OR 子句将成为关闭对此值的过滤的开关。非常方便...

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:

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

回复收藏 0 原文

何必那么矫情 2024-09-11 04:17:22

我认为问题在于

3 + ', ' + 4

将其更改为

'3' + ', ' + '4'

DECLARE @ExcludedList VARCHAR(MAX)

SET @ExcludedList = '3' + ', ' + '4' + ' ,' + '22'

SELECT * FROM A WHERE Id NOT IN (@ExcludedList)

SET @ExcludedListe ，这样您的查询应该变成

要么

SELECT * FROM A WHERE Id NOT IN ('3', '4', '22')

或

SELECT * FROM A WHERE Id NOT IN (3, 4, 22)

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

either

SELECT * FROM A WHERE Id NOT IN ('3', '4', '22')

SELECT * FROM A WHERE Id NOT IN (3, 4, 22)

回复收藏 0 原文

小姐丶请自重 2024-09-11 04:17:22

试试这个：

CREATE PROCEDURE MyProc @excludedlist integer_list_tbltype READONLY AS
  SELECT * FROM A WHERE ID NOT IN (@excludedlist)

然后这样称呼它：

DECLARE @ExcludedList integer_list_tbltype
INSERT @ExcludedList(n) VALUES(3, 4, 22)
exec MyProc @ExcludedList

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

回复收藏 0 原文

秋千易 2024-09-11 04:17:22

我有另一种解决方案，无需动态查询即可完成此操作。
我们也可以借助 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)

这是完整的解决方案：
http://raresql.com/2011/12/21/how-to-use-multiple-values-for-in-clause-using-same-parameter-sql-server/

I have another solution to do it without dynamic query.
We can do it with the help of xquery as well.

    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/

回复收藏 0 原文

~没有更多了~