Execute SQL Server stored procedures sequentially

sql-server, stored-procedures

Solution

build a single batch that invokes all the procedures, using string aggregation, then run this batch. There are many string aggreate concatenation methods, but one of the most effective is the XML blackbox method:

create table #test (spid int, sproc sysname);
insert into #test (spid, sproc) values (1, N'sproc1')
    , (2, N'sproc2')
    , (3, N'sproc3')
    , (4, N'sproc4')
    , (5, N'sproc5')
    , (6, N'sproc6');

declare @sql nvarchar(max);
set @sql = (select N'exec ' + sproc +N';
' from #test 
where spid in (1,3,5) for XML path(''), type).value(N'.', 'nvarchar(max)');

exec sp_executesql @sql;

Problem

I've some case about executing stored procedures in SQL Server. Maybe it's unusual practice, but I've stored a list of stored procedure names in a table. The table is about like this (let's call it `TableFoo`): ``` | SPId | SPName | ------------------- | 1 | spr_name1| | 2 | spr_name2| | 3 | spr_name3| | 4 | spr_name4| ... ``` I want to call/execute a list of stored procedure that generated from the query result on `TableFoo`, the query is about like this: ``` SELECT SPName FROM TableFoo WHERE SPId IN (1, 2, 3) ``` I want to execute the stored procedures sequentially per row I mean I want to do this :) ``` SELECT EXEC(SpName) FROM TableFoo WHERE SPId IN (1, 2, 3) ``` but that does not work It's any solution beside using a `CURSOR` ? Thanks in advance.

Original source