SQL Server存储过程声明一个列表

Question

我的SQL代码非常简单。我正在尝试从数据库中选择一些数据，就像这样：

SELECT * FROM DBTable
WHERE id IN (1,2,5,7,10)

我想知道如何在select中之前声明列表（将其存储在变量、列表、数组或其他东西中），并且仅在select内部使用变量名称，就像这样：

VAR myList = "(1,2,5,7,10)"
SELECT * FROM DBTable
WHERE id IN myList

- Alex Doro

---

你使用的是哪个 SQL Server 版本？ - Pranav Singh

我正在使用 Microsoft SQL Server 2008 R2。 - Alex Doro

可能是 https://dev59.com/qXVD5IYBdhLWcg3wWaRh 的重复问题。 - user1945782

虽然可能存在重复，但这与我的主题无关。MySQL 但可能有帮助。 - OGHaza

Answer 1

您可以像这样将变量声明为临时表：

declare @myList table (Id int)

这意味着您可以使用insert语句向其添加值：

insert into @myList values (1), (2), (5), (7), (10)

然后你的 select 语句可以使用 in 语句：

select * from DBTable
where id in (select Id from @myList)

或者你可以像这样加入到临时表中：

select *
from DBTable d
join @myList t on t.Id = d.Id

如果您经常执行此类操作，那么您可以考虑定义一个用户定义的表类型，然后您就可以这样声明变量：

declare @myList dbo.MyTableType

- Peter Monks

---

谢谢！最终我使用了你的解决方案 :) - Alex Doro

Answer 2

由于in子句需要单独的值而不是包含逗号分隔列表的单个值，因此无法使用普通查询实现该功能。其中一种解决方案是使用动态查询。

declare @myList varchar(100)
set @myList = '1,2,5,7,10'
exec('select * from DBTable where id IN (' + @myList + ')')

- juergen d

---

谢谢！我正想问你如何正确声明它！我会尝试这个。 - Alex Doro

Answer 3

您可以将传递值的列表转换为表值参数，然后对该列表进行选择查询。

DECLARE @list NVARCHAR(MAX)
SET @list = '1,2,5,7,10';

DECLARE @pos INT
DECLARE @nextpos INT
DECLARE @valuelen INT
DECLARE @tbl TABLE (number int NOT NULL)

SELECT @pos = 0, @nextpos = 1;

WHILE @nextpos > 0
BEGIN
    SELECT @nextpos = charindex(',', @list, @pos + 1)
    SELECT @valuelen = CASE WHEN @nextpos > 0
                            THEN @nextpos
                            ELSE len(@list) + 1
                        END - @pos - 1
    INSERT @tbl (number)
        VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
    SELECT @pos = @nextpos;
END

SELECT * FROM DBTable WHERE id IN (SELECT number FROM @tbl);

在这个例子中，传入的字符串'1,2,5,7,10'会被逗号分隔，每个值都会作为新行添加到@tbl表变量中。然后可以使用标准SQL对其进行选择。

如果您打算重复使用此功能，可以进一步将其转换为函数。

- Jonathan

Answer 4

替代 @Peter Monks。

如果'in'语句中的数字较小且固定。

DECLARE @var1 varchar(30), @var2 varchar(30), @var3  varchar(30);

SET @var1 = 'james';
SET @var2 = 'same';
SET @var3 = 'dogcat';

Select * FROM DBTable Where x in (@var1,@var2,@var3);

- Aaron C

Answer 5

在这种情况下，我发现将测试反转对列表更容易操作。例如...

SELECT 
    field0, field1, field2 
FROM 
    my_table 
WHERE 
    ',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%' 

这意味着你所寻找的值不需要复杂的混合匹配。

例如，如果我们的列表是('1,2,3')，那么我们在列表开头和结尾加上逗号，如下所示：',' + @mysearchlist + ','。

我们还要对我们正在寻找的字段值做同样的处理，并添加通配符：'%,' + CAST(field3 AS VARCHAR) + ',%'（请注意%和,字符）。

最后，我们使用LIKE运算符测试这两个值：',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%'。

- user1945782

Answer 6

如果您想将逗号分隔的字符串作为输入并在查询中应用它，那么您可以创建一个函数如下所示：

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
    returns @temptable TABLE (items varchar(MAX))       
    as       
    begin      
        declare @idx int       
        declare @slice varchar(8000)       

        select @idx = 1       
            if len(@String)<1 or @String is null  return       

        while @idx!= 0       
        begin       
            set @idx = charindex(@Delimiter,@String)       
            if @idx!=0       
                set @slice = left(@String,@idx - 1)       
            else       
                set @slice = @String       

            if(len(@slice)>0)  
                insert into @temptable(Items) values(@slice)       

            set @String = right(@String,len(@String) - @idx)       
            if len(@String) = 0 break       
        end   
    return 
    end;

你可以像这样使用它：

Declare @Values VARCHAR(MAX);

set @Values ='1,2,5,7,10';
Select * from DBTable
    Where id  in (select items from [dbo].[Split] (@Values, ',') )

如果您没有逗号分隔的字符串作为输入，您可以尝试使用 Table 变量 或者 TableType 或者 临时表 ，例如：使用列表插入到存储过程中

- Pranav Singh

---

这个适用于所有类型吗？您不需要将来自函数的输出强制转换为INT吗？ - user1945782

@Westie，比较必须是相同类型的。因此，如果dbtable中的id是int类型，则需要进行强制转换。 - Pranav Singh