xenium
asked on
How to execute a list of SQL statements that are generated by a query
Hi,
I have a queries to generate multiple standard SQL statements, eg SELECT SQL_DropTable from..., would give me a list of SQL commands which i then wish to execute. How can i execute these statements automatically using a stored procedure? eg sp_ExecuteSQL('SELECT SQL_DropTable from...') might execute these statements, in the same way that Exec @SQL does for only one statement.
Thanks!
I have a queries to generate multiple standard SQL statements, eg SELECT SQL_DropTable from..., would give me a list of SQL commands which i then wish to execute. How can i execute these statements automatically using a stored procedure? eg sp_ExecuteSQL('SELECT SQL_DropTable from...') might execute these statements, in the same way that Exec @SQL does for only one statement.
Thanks!
you don't need the semicolumn, actually... just ensure you have at least 1 space between the statements...
note, though, that some statements HAVE to be alone (like create procedure, create view ...)
both exec() and sp_executesql can run several statements the same way.
note, though, that some statements HAVE to be alone (like create procedure, create view ...)
both exec() and sp_executesql can run several statements the same way.
ASKER
thanks, but I need to deliver the SQL from a query that generates the SQL, one statement per row, so it won't be structured as above.
ASKER
btw, i didn't realise there is an sp called sp_ExecuteSQL, i thought i invented it for what i want to do! I'd better rename it then, eg sp_ExecBulkSQL('SELECT SQL_DropTable from...')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is fantastic, thankyou very much! (points increased to max 500)
ASKER
btw I think the following PAQ does the same thing, but in a different way: https://www.experts-exchange.com/questions/22907647/How-to-execute-a-series-of-SQL-statements-that-are-generated-from-a-query.html
declare @test varchar(1000)
set @test = 'select 1; select 2; select 3;'
exec(@test);
Just separate them with semi-colons;