T-SQL：循环遍历已知值数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1589214/
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
T-SQL: Looping through an array of known values
提问by John
Here's my scenario:
这是我的场景：
Let's say I have a stored procedure in which I need to call another stored procedure on a set of specific ids; is there a way to do this?
假设我有一个存储过程，我需要在其中对一组特定的 id 调用另一个存储过程；有没有办法做到这一点？
i.e. instead of needing to do this:
即而不是需要这样做：
exec p_MyInnerProcedure 4
exec p_MyInnerProcedure 7
exec p_MyInnerProcedure 12
exec p_MyInnerProcedure 22
exec p_MyInnerProcedure 19
Doing something like this:
做这样的事情：
*magic where I specify my list contains 4,7,12,22,19*
DECLARE my_cursor CURSOR FAST_FORWARD FOR
*magic select*
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @MyId
WHILE @@FETCH_STATUS = 0
BEGIN
exec p_MyInnerProcedure @MyId
FETCH NEXT FROM my_cursor INTO @MyId
END
My Main goal here is simply maintainability (easy to remove/add id's as the business changes), being able to list out all Id's on a single line... Performance shouldn't be as big of an issue
我的主要目标是简单的可维护性（随着业务变化易于删除/添加 id），能够在一行中列出所有 Id ......性能不应该是一个大问题
回答by Adam Robinson
declare @ids table(idx int identity(1,1), id int)
insert into @ids (id)
select 4 union
select 7 union
select 12 union
select 22 union
select 19
declare @i int
declare @cnt int
select @i = min(idx) - 1, @cnt = max(idx) from @ids
while @i < @cnt
begin
select @i = @i + 1
declare @id = select id from @ids where idx = @i
exec p_MyInnerProcedure @id
end
回答by Charles Bretana
What I do in this scenario is create a table variable to hold the Ids.
在这种情况下，我所做的是创建一个表变量来保存 Id。
Declare @Ids Table (id integer primary Key not null)
Insert @Ids(id) values (4),(7),(12),(22),(19)
-- (or call another table valued function to generate this table)
--（或者调用另一个表值函数来生成这个表）
Then loop based on the rows in this table
然后基于此表中的行进行循环
Declare @Id Integer
While exists (Select * From @Ids)
Begin
Select @Id = Min(id) from @Ids
exec p_MyInnerProcedure @Id
Delete from @Ids Where id = @Id
End
or...
或者...
Declare @Id Integer = 0 -- assuming all Ids are > 0
While exists (Select * From @Ids
where id > @Id)
Begin
Select @Id = Min(id)
from @Ids Where id > @Id
exec p_MyInnerProcedure @Id
End
Either of above approaches is much faster than a cursor (declared against regular User Table(s)). Table-valued variables have a bad rep because when used improperly, (for very wide tables with large number of rows) they are not performant. But if you are using them only to hold a key value or a 4 byte integer, with a index (as in this case) they are extremely fast.
上述任何一种方法都比游标（针对常规用户表声明）快得多。表值变量的表现不佳，因为如果使用不当（对于具有大量行的非常宽的表），它们的性能不佳。但是，如果您仅使用它们来保存键值或 4 字节整数，并且带有索引（如本例所示），它们将非常快。
回答by Peter Radocchia
use a static cursor variable and a split function:
使用静态游标变量和拆分函数：
declare @comma_delimited_list varchar(4000)
set @comma_delimited_list = '4,7,12,22,19'
declare @cursor cursor
set @cursor = cursor static for
select convert(int, Value) as Id from dbo.Split(@comma_delimited_list) a
declare @id int
open @cursor
while 1=1 begin
fetch next from @cursor into @id
if @@fetch_status <> 0 break
....do something....
end
-- not strictly necessary w/ cursor variables since they will go out of scope like a normal var
close @cursor
deallocate @cursor
Cursors have a bad rep since the default options when declared against user tables can generate a lot of overhead.
游标的表现不佳，因为针对用户表声明的默认选项会产生大量开销。
But in this case the overhead is quite minimal, less than any other methods here. STATIC tells SQL Server to materialize the results in tempdb and then iterate over that. For small lists like this, it's the optimal solution.
但在这种情况下，开销非常小，比这里的任何其他方法都要少。STATIC 告诉 SQL Server 在 tempdb 中实现结果，然后对其进行迭代。对于像这样的小列表，这是最佳解决方案。
回答by Ramakrishna Talla
You can try as below :
您可以尝试如下：
declare @list varchar(MAX), @i int
select @i=0, @list ='4,7,12,22,19,'
while( @i < LEN(@list))
begin
declare @item varchar(MAX)
SELECT @item = SUBSTRING(@list, @i,CHARINDEX(',',@list,@i)-@i)
select @item
--do your stuff here with @item
exec p_MyInnerProcedure @item
set @i = CHARINDEX(',',@list,@i)+1
if(@i = 0) set @i = LEN(@list)
end
回答by kristof
I usually use the following approach
我通常使用以下方法
DECLARE @calls TABLE (
id INT IDENTITY(1,1)
,parameter INT
)
INSERT INTO @calls
select parameter from some_table where some_condition -- here you populate your parameters
declare @i int
declare @n int
declare @myId int
select @i = min(id), @n = max(id) from @calls
while @i <= @n
begin
select
@myId = parameter
from
@calls
where id = @i
EXECUTE p_MyInnerProcedure @myId
set @i = @i+1
end
回答by Moshe
CREATE TABLE #ListOfIDs (IDValue INT)
DECLARE @IDs VARCHAR(50), @ID VARCHAR(5)
SET @IDs = @OriginalListOfIDs + ','
WHILE LEN(@IDs) > 1
BEGIN
SET @ID = SUBSTRING(@IDs, 0, CHARINDEX(',', @IDs));
INSERT INTO #ListOfIDs (IDValue) VALUES(@ID);
SET @IDs = REPLACE(',' + @IDs, ',' + @ID + ',', '')
END
SELECT *
FROM #ListOfIDs
回答by LoMaPh
Make a connection to your DB using a procedural programming language (here Python), and do the loop there. This way you can do complicated loops as well.
使用过程编程语言（此处为 Python）连接到您的数据库，并在那里执行循环。这样你也可以做复杂的循环。
# make a connection to your db
import pyodbc
conn = pyodbc.connect('''
Driver={ODBC Driver 13 for SQL Server};
Server=serverName;
Database=DBname;
UID=userName;
PWD=password;
''')
cursor = conn.cursor()
# run sql code
for id in [4, 7, 12, 22, 19]:
cursor.execute('''
exec p_MyInnerProcedure {}
'''.format(id))
