DanP1980
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER