Solved

Execute a list of stored procedures from a query result

Posted on 2014-02-07
3
229 Views
Last Modified: 2014-02-07
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
Comment
Question by:DanP1980
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:DanP1980
Comment Utility
Great solution - thanks!
0
 

Author Comment

by:DanP1980
Comment Utility
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now