CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqldynamic-sql
Thomas
Thomas

Reputation: 34208

dynamic sql and store output in variable

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

Upvotes: 0

Views: 8889

Answers (3)

ashish.chotalia
ashish.chotalia

Reputation: 3746

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

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107816

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

Upvotes: 3

Ralph Shillington
Ralph Shillington

Reputation: 21108

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.

Upvotes: 0

Related Questions