dynamic sql and store output in variable

Question

declare @SQL nvarchar(100)
set @SQL = ''

select @SQL = @SQL + FieldName + ' as [' + Description + '], '
from FieldsInfo
where TableName = 'Emp'

set @SQL = 'select '+ left(@SQL, len(@SQL)-1) + ' from Emp FOR XML AUTO, ELEMENTS, ROOT(''customers'')'

exec sp_executesql @SQL

above is dynamic sql and when it is executed by sp_executesql then i got output as as xml. if i want to store that xml in a variable. so then what i need to add in my tsql script....please help

possible duplicate of [How to get sp_executesql result into a variable?](http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable) — Robert Koritnik, Jan 20 '11 at 17:32

score 3 · Accepted Answer · answered Jan 20 '11 at 18:44

For anyone else trying to follow the question, here are some sample tables to use

create table emp (a varchar(10), b int, id int identity)
insert emp select 'abc', 1
insert emp select 'def', 2
create table fieldsinfo (tablename sysname, description sysname, fieldname sysname)
insert fieldsinfo select 'emp', 'field 1', 'a'
insert fieldsinfo select 'emp', 'field 2', 'b'

This script stores the generated XML into the variable @XML (original!)

declare @SQL nvarchar(max)
set @SQL = ''

select @SQL = @SQL + FieldName + ' as [' + Description + '], '
from FieldsInfo
where TableName = 'Emp'

set @SQL = 'set @XML = (select '+ left(@SQL, len(@SQL)-1) + ' from Emp FOR XML AUTO, ELEMENTS, ROOT(''customers''))'

declare @Xml xml
exec sp_executesql @SQL, N'@XML xml output', @xml output

select 'I have >>> ', @Xml   -- check contents

sorry ur trick does not work. please check once in sql server management studio. here i try according to declare @Xml xml declare @SQL nvarchar(100) set @SQL = 'select * from Emp FOR XML AUTO, ELEMENTS, ROOT(''customers'')' exec sp_executesql @SQL,N'@XML xml output', @xml output select @xml but when i issue select @xml it shows null instead of xml data. — Thomas, Jan 20 '11 at 18:52

score 1 · Answer 2 · answered Jan 21 '11 at 09:34

Try this as well:

declare @SQL nvarchar(1000)
set @SQL = ''

select @SQL = @SQL + FieldName + ' as [' + Description + '], '
from FieldsInfo
where TableName = 'Emp'


DECLARE @ParmDefinition nvarchar(1000);
DECLARE @XMLValueString varchar(1000);
SET @ParmDefinition = N'@XMLValue varchar(1000) OUTPUT';
set @SQL = 'SELECT @XMLValue = (select '+ left(@SQL, len(@SQL)-1) + ' from Emp FOR XML AUTO, ELEMENTS, ROOT(''customers''))'
print @SQL 
exec sp_executesql @SQL,@ParmDefinition, @XMLValue=@XMLValueString output
SELECT @XMLValueString

score 0 · Answer 3 · answered Jan 20 '11 at 17:34

There isn't really any way of getting from the dynamic sql back to the calling process without it smelling like a huge hack.

If you absolutely must, I suppose you could have a table that your script could write it's values to, and your proc could then read from.

you may want to consider doing your dynamic stuff outside of sql server, but even that is fraught with peril.

dynamic sql and store output in variable

3 Answers3