T-SQL:循环遍历已知值数组
it1352
帮助1人
问题说明
这是我的场景:
假设我有一个存储过程，我需要在其中对一组特定的 id 调用另一个存储过程；有没有办法做到这一点?
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?
即而不是需要这样做:
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)，能够在一行中列出所有 ID...性能不应该是一个大问题
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
正确答案
#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
这篇好文章是转载于：学新通技术网
