Solved

Execute a list of stored procedures from a query result

Posted on 2014-02-07
3
234 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
ID: 39842052
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
ID: 39842107
Great solution - thanks!
0
 

Author Comment

by:DanP1980
ID: 39842127
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Statement to Update Email Domain 2 29
How to revise an Access 2003 query into a SQL Server 2008 SQL statement? 1 30
sql query 8 49
Challenging SQL Update 5 41
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

776 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