T-SQL:在已知值的数组中循环

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……性能不应该是一个大问题

6 个解决方案

#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

#2

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字节的整数，并使用索引(在本例中)，那么它们的速度将非常快。

#3

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.

但是在这种情况下，开销非常小，比这里的任何其他方法都要小。静态命令SQL Server在tempdb中实现结果，然后进行迭代。对于这样的小列表，它是最优解。

#4

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

#5

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

#6

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