SQL Server 过程声明一个列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21160456/
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 procedure declare a list
提问by Alex Doro
My SQL code is fairly simple. I'm trying to select some data from a database like this:
我的 SQL 代码相当简单。我正在尝试从这样的数据库中选择一些数据：
SELECT * FROM DBTable
WHERE id IN (1,2,5,7,10)
I want to know how to declare the list before the select (in a variable, list, array, or something) and inside the select only use the variable name, something like this:
我想知道如何在选择之前（在变量、列表、数组或其他东西中）声明列表，而在选择内部仅使用变量名称，如下所示：
VAR myList = "(1,2,5,7,10)"
SELECT * FROM DBTable
WHERE id IN myList
回答by Peter Monks
You could declare a variable as a temporary table like this:
您可以像这样将变量声明为临时表：
declare @myList table (Id int)
Which means you can use the insertstatement to populate it with values:
这意味着您可以使用该insert语句用值填充它：
insert into @myList values (1), (2), (5), (7), (10)
Then your selectstatement can use either the instatement:
那么您的select语句可以使用以下任一in语句：
select * from DBTable
where id in (select Id from @myList)
Or you could join to the temporary table like this:
或者你可以像这样加入临时表：
select *
from DBTable d
join @myList t on t.Id = d.Id
And if you do something like this a lot then you could consider defining a user-defined table typeso you could then declare your variable like this:
如果你经常做这样的事情，那么你可以考虑定义一个用户定义的表类型，这样你就可以像这样声明你的变量：
declare @myList dbo.MyTableType
回答by juergen d
That is not possible with a normal query since the inclause needs separate values and not a single value containing a comma separated list. One solution would be a dynamic query
这对于普通查询是不可能的，因为in子句需要单独的值，而不是包含逗号分隔列表的单个值。一种解决方案是动态查询
declare @myList varchar(100)
set @myList = '(1,2,5,7,10)'
exec('select * from DBTable where id IN ' + @myList)
回答by Jonathan
You can convert the list of passed values into a table valued parameter and then select against this list
您可以将传递的值列表转换为表值参数，然后根据此列表进行选择
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);
In this example the string passed in '1,2,5,7,10'is split by the commas and each value is added as a new row within the @tbltable variable. This can then be selected against using standard SQL.
在此示例中，传入'1,2,5,7,10'的字符串由逗号分隔，并且每个值都作为@tbl表变量中的新行添加。然后可以选择使用标准 SQL。
If you intend to reuse this functionality you could go further and convert this into a function.
如果您打算重用此功能，则可以进一步将其转换为函数。
回答by Pranav Singh
If you want input comma separated string as input & apply in in query in that then you can make Function like:
如果您想输入逗号分隔的字符串作为输入并在查询中应用，那么您可以使 Function 如下：
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;
You can use it like :
你可以像这样使用它：
Declare @Values VARCHAR(MAX);
set @Values ='1,2,5,7,10';
Select * from DBTable
Where id in (select items from [dbo].[Split] (@Values, ',') )
Alternatively if you don't have comma-separated string as input, You can try Table variableOR TableTypeOr Temp tablelike: INSERT using LIST into Stored Procedure
或者，如果您没有逗号分隔的字符串作为输入，您可以尝试Table variableORTableType或Temp table类似：INSERT using LIST into Stored Procedure
回答by Paul
I've always found it easier to invert the test against the list in situations like this. For instance...
我一直发现在这种情况下更容易根据列表反转测试。例如...
SELECT
field0, field1, field2
FROM
my_table
WHERE
',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%'
This means that there is no complicated mish-mash required for the values that you are looking for.
这意味着您正在寻找的值不需要复杂的混搭。
As an example, if our list was ('1,2,3'), then we add a comma to the start and end of our list like so: ',' + @mysearchlist + ','.
举个例子，如果我们的名单是('1,2,3')，那么我们一个逗号添加到我们的列表的开始和结束，像这样：',' + @mysearchlist + ','。
We also do the same for the field value we're looking for and add wildcards: '%,' + CAST(field3 AS VARCHAR) + ',%'(notice the %and the ,characters).
我们也对要查找的字段值执行相同操作并添加通配符：（'%,' + CAST(field3 AS VARCHAR) + ',%'注意%和,字符）。
Finally we test the two using the LIKEoperator: ',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%'.
最后，我们使用LIKE运算符测试两者：',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%'。
回答by Aaron C
Alternative to @Peter Monks.
替代@Peter Monks。
If the number in the 'in' statement is small and fixed.
如果'in'语句中的数字很小并且是固定的。
DECLARE @var1 varchar(30), @var2 varchar(30), @var3 varchar(30);
SET @var1 = 'james';
SET @var2 = 'same';
SET @var3 = 'dogcat';
Select * FROM Database Where x in (@var1,@var2,@var3);
