• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

Execute a list of stored procedures from a query result

I'm wondering if it possible to execute (possibly through dynamic sql) a number of stored procedures returned from a query.  

For example if a query was run to retrieve the name of stored procedures from sysobjects that meet a criteria and the names minus the prefix sp_ was returned could these be used to geenrate a exec SQL statement whereby the stored procedures returned could then be run.

So the query result set from:

select SUBSTRING(name,4,20) from sysobjects where id in ( select ID from syscomments where ltrim(Rtrim([text])) like '%tblSales%')



Might return the 2 stored procedures
SALESWEEKLY
SALESMONTHLY

which are actually sp_SALESWEEKLY AND sp_SALESMONTHLY

Could code then be produced to run the initial query and then execute all the procedures returned from it.

Something like

DECLARE @SQL VARCHAR(max)

Set @SQL = 'EXEC SP_' + (first Stored Procedure name)

and then loop until all are executed.

I think this would be very useful if it could be achieved.


Many thanks
0
DanP1980
Asked:
DanP1980
  • 2
1 Solution
 
Surendra NathTechnology LeadCommented:
Yes it can be done, by using a cursor as below

DECLARE @SQL NVARCHAR(2000)
DECLARE @SPName NVARCHAR(50)
DECLARE T CURSOR 
FOR 
select SUBSTRING(name,4,20) from sysobjects where id in ( select ID from syscomments where ltrim(Rtrim([text])) like '%tblSales%') 
OPEN T 
FETCH T INTO @SPName
WHILE @@FETCH_STATUS = 0 
BEGIN
 SET @SQL = ' 'EXEC SP_' +@SPName
 EXEC (@SQL)
 FETCH T INTO @SPName
END
CLOSE T
DEALLOCATE T

Open in new window

0
 
DanP1980Author Commented:
Great solution - thanks!
0
 
DanP1980Author Commented:
One too may quotation marks:

Should be

DECLARE @SQL NVARCHAR(2000)
DECLARE @SPName NVARCHAR(50)
DECLARE T CURSOR
FOR
select SUBSTRING(name,4,20) from sysobjects where id in ( select ID from syscomments where ltrim(Rtrim([text])) like '%milking%')
OPEN T
FETCH T INTO @SPName
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @SQL = 'EXEC SP_'  +@SPName
 EXEC (@SQL)
 FETCH T INTO @SPName
END
CLOSE T
DEALLOCATE T
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now