How to run dynamic query which is the output of another query ?

Question

How to run dynamic query which is the output of another query ?

vignesh.ms

Hall of Fame

Points: 3456
More actions
March 21, 2014 at 10:39 am

#396463

Hello Experts,
I'm facing a situation like generating query like follows ,
select top 3 'select top 3 * from '+name from sysobjects where type = 'u'
I wanted to run all at once(without copy paste the output)
something like
execute (select top 3 'select top 3 * from '+name from sysobjects where type = 'u')

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Assign it to a variable and execute the variable with sp_executesql or EXEC().

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 2

Luis Cazares (3/21/2014)
Assign it to a variable and execute the variable with sp_executesql or EXEC().

how to assign all at once ?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

There are 2 options available:

DECLARE @sql varchar(8000) = '';

--Option 1

select top 3

@sql = @sql + 'select top 3 * from '+name + CHAR(10)

from sysobjects where type = 'u';

--Option 2

SELECT @sql = (select top 3

'select top 3 * from '+name + CHAR(10)

from sysobjects where type = 'u'

FOR XML PATH(''));

EXEC(@SQL);

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Here is how I would do it. First of all, since you appear to be running SQL Server 2008, I would not use sysobjects.

declare @SQLCmd nvarchar(max);

select @SQLCmd = stuff((select N'select top 3 * from ' + schema_name(tab.schema_id) + N'.' + tab.name + nchar(13) + nchar(10)

from sys.tables tab -- lists all user table in current database

order by tab.name -- if you want this in alphabetical order by table name

for xml path(''),TYPE).value('.','nvarchar(max)'),1,0,'');

select cast(@SQLCmd as xml); -- double click on the link in the results to review the code

exec sp_executesql @SQLCmd; -- Runs the commands. You may need to do this with SSMS set to send results to text depending on how many tables you have.