Execute SQL Server stored procedures sequentially

Question

I have a case of executing stored procedures in SQL Server.

This may be an unusual practice, but I kept the list of stored procedure names in a table. The table is something 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 the list of stored procedure generated from the query result on TableFoo , the query is something like this:

 SELECT SPName FROM TableFoo WHERE SPId IN (1, 2, 3) 

I want to execute stored procedures sequentially in a string

I want to say that I want to do this :)

 SELECT EXEC(SpName) FROM TableFoo WHERE SPId IN (1, 2, 3) 

but it does not work

Is this any solution other than using CURSOR ?

Thanks in advance.

Answer 1

create one batch that calls all procedures using row aggregation, and then run that batch. There are many aggreate string concatenation methods, but one of the most efficient is the blackbox XML 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; 

Answer 2

You cannot perform as part of the selected request. A cursor can help you meet your needs, however this seems too complicated. If you are developing an application, you might want to put this logic higher in the architecture.

Hi

Answer 3

  Declare @SPCall table (id int,SP varchar(20)) Insert into @SPCall values (1,'sp1'),(2,'sp3'),(3,'sp3') Declare @spName varchar(20) Declare @value int set @value =1 While @value<=(Select COUNT(*) from @SPCall) BEGIN Set @spName=(select Sp from @SPCall where id=@value ) exec @spName set @ value=@value +1 END 

Answer 4

First of all, this is not a very good approach. But if you insist on it, you can use dynamic sql in saved proc. Dumping the list of all SP names into the temp table and using the loop in this table passes each of the sp names to the dynsmic sql query. Dynamically building a T-SQL statement can be done using the EXECUTE Command or sp_executesql. I would suggest you go to the last one, i.e. Sp_executesql.

The basic syntax for using sp_executesql is:

 sp_executesql [@SQLStatement],[@ParameterDefinitionList],[@ParameterValueList] 

@ParameterDefinition is used to specify the format of the parameter before executing the SQL string. You can get many examples for this on google. Hope this helps.

Answer 5

I have another alternative

 DECLARE @SQL VARCHAR(8000) SELECT @SQL='' SELECT @SQL = @SQL+ 'Exec ' + b.SPname FROM TbFoo WHERE SpId IN (1,2,3) EXEC(@SQL) 

But is it good?