T-SQL:循环遍历已知值数组

2021-09-10 00:00:00 sql tsql sql-server

这是我的场景:

假设我有一个存储过程，我需要在其中对一组特定的 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

推荐答案

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

相关文章