mysql循环遍历已知值数组_sql server - T-SQL：循环遍历已知值的数组

sql server - T-SQL：循环遍历已知值的数组

这是我的情景：

假设我有一个存储过程，我需要在一组特定的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)，能够在一行中列出所有Id ...性能不应该是一个大问题

6个解决方案

84 votes

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

Adam Robinson answered 2019-06-11T01:17:21Z

39 votes

我在这种情况下做的是创建一个表变量来保存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

上述方法中的任何一种都比游标快得多(对常规用户表声明)。 表值变量具有错误的rep，因为当使用不当时(对于具有大量行的非常宽的表)，它们不具有高性能。 但是如果你只使用它们来保存键值或4字节整数，并带有索引(如本例所示)它们非常快。

Charles Bretana answered 2019-06-11T01:18:08Z

15 votes

使用静态游标变量和拆分函数：

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

游标具有错误的rep，因为在针对用户表声明时，默认选项会产生大量开销。

但在这种情况下，开销很小，比这里的任何其他方法都少。 STATIC告诉SQL Server在tempdb中实现结果，然后迭代它。 对于像这样的小型列表，它是最佳解决方案。

Peter Radocchia answered 2019-06-11T01:18:46Z

4 votes

我通常使用以下方法

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

kristof answered 2019-06-11T01:19:09Z

2 votes

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

Moshe answered 2019-06-11T01:19:26Z

2 votes

你可以尝试如下：

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

Ramakrishna Talla answered 2019-06-11T01:19:50Z