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!

ExistenceExists

Structure your query like this example:

declare @test varchar(1000)
set @test = 'select 1; select 2; select 3;'

exec(@test);

Just separate them with semi-colons;

Guy Hengel [angelIII / a3]

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.

xenium

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.

xenium

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

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xenium

ASKER

This is fantastic, thankyou very much! (points increased to max 500)

xenium

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