dynamic sql and store output in variable sql server2005

[Tridip Bhattacharjee]

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

tbhattacharjee

[Corporal Agarn]

You would normally use a dynamic code like this for interface with a front end like .NET. What you need to do is add INTO mytable before the FROM or define a table and use INSERT INTO mytable.

[Costica U]

Replace "exec sp_executesql @SQL" with:

declare @xml xml
set @SQL = 'set @xml =('+@SQL+')' -- stores query result to @xml variable
exec sp_executesql @SQL,N'@SQL nvarchar(4000),@xml xml OUTPUT',@SQL,@xml OUTPUT
--- display result
print convert(varchar(MAX),@xml)