动态sql并将输出存储在变量中

发布于 10-14 00:08 字数 397 浏览 9 评论 0原文

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

上面是动态sql,当它由sp_executesql执行时,我得到了xml格式的输出。 如果我想将该 xml 存储在变量中。那么我需要在 tsql 脚本中添加什么......请帮助

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

慈悲佛祖2024-10-21 00:08:34

对于任何其他试图解决这个问题的人,这里有一些可以使用的示例表。

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'

此脚本将生成的 XML 存储到变量 @XML (原始!)

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

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
烟火散人牵绊2024-10-21 00:08:34

也试试这个:

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

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
凯凯我们等你回来2024-10-21 00:08:34

实际上没有任何方法可以从动态 sql 回到调用过程,而不让人感觉像是一个巨大的 hack。

如果您绝对必须这样做,我想您可以有一个表,您的脚本可以将其值写入其中,然后您的过程可以从中读取。

您可能想考虑在 sql server 之外进行动态操作,但即使这样也充满危险。

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文