CODEWITHSUNDEEP
sql-serverstored-procedures
Willy Lazuardi
Willy Lazuardi

Reputation: 1816

Execute SQL Server stored procedures sequentially

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.

Upvotes: 5

Views: 6361

Answers (5)

Willy Lazuardi
Willy Lazuardi

Reputation: 1816

I've another alternative too

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 enough?

Upvotes: 1

Saurabh R S
Saurabh R S

Reputation: 3177

First of all this is not a good approach. But if you insist to go by that way you can make use of dynamic sql in a stored proc. Dump a list of all the SP names into a temp table and using a loop on that table pass each of the sp names into a dynsmic sql query. A dynamically build T-SQL statement can be executed using EXECUTE Command or sp_executesql statement. I would suggest you to go for the latter i.e. sp_executesql.

The basic syntax for using sp_executesql:

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

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

Upvotes: 0

praveen
praveen

Reputation: 12271

  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

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294297

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;

Upvotes: 5

Peter
Peter

Reputation: 56

You can't execute within a select query. A cursor could enable you to meet your needs, however, it does seem overly complex. If you're developing an application, you might want to place this logic higher up in the architecture.

Regards

Upvotes: 0

Related Questions