Execute a list of stored procedures from a query result
Posted on 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
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.
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.