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

What I do in this scenario is create a table variable to hold the Ids.

  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.

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

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.

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

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

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

Make a connection to your DB using a procedural programming language (here Python), and do the loop there. This way you can do complicated loops as well.

# make a connection to your db
import pyodbc
conn = pyodbc.connect('''
Driver={ODBC Driver 13 for SQL Server};
Server=serverName;
Database=DBname;
UID=userName;
PWD=password;
''')
cursor = conn.cursor()


# run sql code
for id in [4, 7, 12, 22, 19]:
cursor.execute('''
exec p_MyInnerProcedure {}
'''.format(id))