I have a query that runs against a sql server database that produces a sql statement for each row of the query returned.
EG.
DROP PROCEDURE dbo.[Proc1]
DROP PROCEDURE dbo.[Proc2]
etc
In the same script, how can I execute the sql returned in the query?
Check out sp_executesql
One way is, you'll have to iterate through your result set and use sp_executesql. Just curious though, what are you trying to accomplish
Dump the results into a variable of type varchar(max) or nvarchar(max) and then EXEC them
-- Create a terrible, horrible cursor to run the statements
-- You have just now killed a kitten
DECLARE Csr cursor for
SELECT
P.name AS proc_name
, schema_name(P.schema_id) AS schemaname
FROM
sys.procedures P
DECLARE @query varchar(max), @proc_name sysname, @schema sysname
OPEN Csr
FETCH NEXT FROM Csr INTO
@proc_name, @schema
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
BEGIN TRY
-- build out the drop statement, may not be necc
SET @query = 'DROP PROCEDURE' + QUOTENAME(@schema) + '.' + QUOTENAME(@proc_name)
-- Actually run the dynamic query
EXECUTE(@query)
END TRY
BEGIN CATCH
-- Be good and note what failed, fix this manually
PRINT 'This query failed'
PRINT @query
END CATCH
FETCH NEXT FROM Csr INTO
@proc_name, @schema
END
END
CLOSE Csr
DEALLOCATE Csr
