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
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
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
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.
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