111

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?

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

Improve this question
1

7 Answers 7

Reset to default
129 
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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I was hoping there would be a more elegant way, but I think this will be as close as I can get: Ended up using a hybrid between using the select/unions here, and the cursor from the example. Thanks!
@john: if you're using 2008, you can do something like INSERT @ids VALUES (4), (7), (12), (22), (19)
Just FYI, memory tables like this are generally faster than cursors (though for 5 values I can hardly see that making any difference), but the biggest reason I like them is that I find the syntax similar to what you'd find in application code, whereas cursors appear (to me) to be relatively different.
though it will in practise harm performance only very little i want to point out that this iterates through all numbers within the defined space. the solution below with While exists (Select * From @Ids)... is logically more sound (and more elegant).
70

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.

Improve this answer

2 Comments

Above approach is equivalent to or slower than a cursor declared on a table variable. It is certainly not faster. It would be faster than a cursor declared w/ default options on regular user tables, though.
@Peter, ahhh, yes you are correct, I incorrectly assume that using a cursor implies a regular user table, not a table variable.. Ihave edited to make clear the distinction
25

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 string_split(@comma_delimited_list, ',')
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.

Improve this answer

Comments

8

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
Improve this answer

2 Comments

I'd make that list declaration like this : @list ='4,7,12,22,19' + ',' - so that's it's totally clear that the list has to end with a comma (it doesn't work without it!).
I fail to see any imperfection in this answer except we have two selects
5

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
Improve this answer

Comments

2 
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
Improve this answer

Comments

1

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))
Improve this answer

1 Comment

After looking at all these TSQL examples I had just come to that conclusion and was on my way to PyCharm when I see your answer.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.