?
Solved

Execute a list of stored procedures from a query result

Posted on 2014-02-07
3
Medium Priority
?
241 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

752 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