这里是我的情况：
比方说，我有一个存储过程中，我需要调用一组特定的ID的另一个存储过程; 有没有办法做到这一点？
即，而不是需要做到这一点：
exec p_MyInnerProcedure 4
exec p_MyInnerProcedure 7
exec p_MyInnerProcedure 12
exec p_MyInnerProcedure 22
exec p_MyInnerProcedure 19
做这样的事情：
*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
我在这里的主要目的仅仅是可维护性（容易删除/添加的id为业务变化），能够列出在一行中的所有标识的......性能应该不会像什么大问题
Answer 1:
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
Answer 2:
我在这种情况下做的就是创建一个表变量来保存的ID。
Declare @Ids Table (id integer primary Key not null)
Insert @Ids(id) values (4),(7),(12),(22),(19)
- （或拨打另一个表值函数生成此表）
然后根据此表中的行中循环
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
要么...
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
任一的上述方法是比光标（针对定期用户表（一个或多个）声明）快得多。 表值变量有一个坏名声，因为使用不当的时候，（对于大量的行很宽表）他们不是高性能。 但是，如果你只使用他们举行一个键值或4字节的整数，一个指数（如在这种情况下），他们是非常快。
Answer 3:
使用静态游标变量和一个分裂功能 ：
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
游标有因为对用户表声明可以产生大量的开销时，默认选项的坏名声。
但在这种情况下的开销是相当小的，不到这里的任何其他方法。 STATIC告诉SQL Server兑现在tempdb结果，然后遍历这一点。 对于这样的小名单，这是最佳的解决方案。
Answer 4:
我通常用下面的办法
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
Answer 5:
您可以尝试如下：
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
Answer 6:
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
文章来源: T-SQL: Looping through an array of known values
