How to execute an output of a select statement?

[hudi]

Hi,

For example i have a select statement that generates me a several new select statement and I would like to execute them!

That means that I would like to execute the output, result of the select statement!

Anyone?

bye
Andrej

 

[RickCole]

Youe need to use what they call dynamic SQL where you put you statements into a string within a stored porcdure and then execute the procedure. Something like this:


CREATE PROCEDURE Test
AS

--Start of procedure
declare @sql varchar (2000)
select @sql = 'select * from tablename'
exec (@sql)
--End of procdure


Execute Test

Rick.

 

[hudi]

Well I found that, but now I have another problem on which I didnt think before!

My output is about 100 select statements which are pretty long. And I have to execute them by using UNION ALL clause!

I mean, I need the union of all my select statements which I get from my output!

So, there is a problem with declaration, cause you're not allowed to declare variable as ntext or text!

what to do??

bye
Andrej

 

[RickCole]

That's one pretty big SQL statement if it's going over 8,000 characters. I think if it was that large then I would be concerned as to how efficient the query would be.

Can you not split this down into more then one @sql variable?? You could maybe use some temporary tables and then use a final @sql variable to select the results from them.

Rick.

 

[moonshadow]

Sounds to me like your database needs redesigning. But if you REALLY want to do this, I suggest using a cursor and a temporary table. In script, first create a temporary table to hold the output, then open a cursor based on your original query. Looping through the output of your query, one record at a time, construct a piece of SQL to append records into the temporary table, using the select statement as the source of the records. When you've processed all your select statements, close and deallocate the cursor, and just do select * from temporary table. Lastly, drop the temporary table. I have done this before, but I think you really have to justify it to yourself, and that there isn't a completely different way of doing this which is more practical.