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

时间:2022-11-24
本文介绍了T-SQL:循环遍历已知值数组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

这是我的场景:

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

这篇关于T-SQL:循环遍历已知值数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持html5模板网!

上一篇:如何从查询中返回 SQL 数据类型? 下一篇:T-SQL 中是否有三元条件运算符?

相关文章

最新文章