how do i get returned columns into a comma delimited string? I'm trying to get @sql to be used but had to manually type in the values.
如何将返回的列转换为逗号分隔的字符串?我正在尝试使用@sql但必须手动输入值。
declare @orderId as int, @sql AS VARCHAR(MAX)
set @orderId = 10
set @sql = 'select orderId, categoryId, itemId from Orders where orderId = ' + CONVERT(varchar(10), @orderId)
--select @sql as 'sql stmt' --select orderId, categoryId, itemId from Orders where orderId = 10
--execute(@sql)
--will return as:
--orderId categoryId itemId
--10 281 1054
declare @dt table(orderId varchar(10), categoryId varchar(10), itemId varchar(10))
--insert @dt values (@sql)
insert @dt values ('10','281','1054') -- did this to get results but want to use @sql
--how to get the @sql to be a string as '10','281','1054'
select '{"orderinfo" : [' + STUFF((
select
',{"Order Id":' + orderId
+ ',"Category Id":' + categoryId
+ ',"Item Id":' + itemId
+'}'
from @dt
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, '') + ']}' as jsonData
--end results: {"orderinfo" : [{"Order Id":10,"Category Id":281,"Item Id":1054}]}
I'm mostly interested in returning json formatted data.
我最感兴趣的是返回json格式的数据。
2 个解决方案
#1
0
declare @orderId as int, @sql AS VARCHAR(MAX)
set @orderId = 10
set @sql = 'select orderId, categoryId, itemId from Orders where orderId = ' + CONVERT(varchar(10), @orderId)
declare @dt table(orderId varchar(10), categoryId varchar(10), itemId varchar(10))
insert into @dt exec(@sql)
-- this is what you needed
select '{"orderinfo" : [' + STUFF((
select
',{"Order Id":' + orderId
+ ',"Category Id":' + categoryId
+ ',"Item Id":' + itemId
+'}'
from @dt
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, '') + ']}' as jsonData
--end results: {"orderinfo" : [{"Order Id":10,"Category Id":281,"Item Id":1054}]}
#2
0
I suggest you create a scalar-function that uses a cursor that fetches and builds a delineated string.
我建议你创建一个标量函数,它使用一个游标来获取和构建一个描述的字符串。
Then call your function on the select of your query.
然后在选择查询时调用您的函数。
#1
0
declare @orderId as int, @sql AS VARCHAR(MAX)
set @orderId = 10
set @sql = 'select orderId, categoryId, itemId from Orders where orderId = ' + CONVERT(varchar(10), @orderId)
declare @dt table(orderId varchar(10), categoryId varchar(10), itemId varchar(10))
insert into @dt exec(@sql)
-- this is what you needed
select '{"orderinfo" : [' + STUFF((
select
',{"Order Id":' + orderId
+ ',"Category Id":' + categoryId
+ ',"Item Id":' + itemId
+'}'
from @dt
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, '') + ']}' as jsonData
--end results: {"orderinfo" : [{"Order Id":10,"Category Id":281,"Item Id":1054}]}
#2
0
I suggest you create a scalar-function that uses a cursor that fetches and builds a delineated string.
我建议你创建一个标量函数,它使用一个游标来获取和构建一个描述的字符串。
Then call your function on the select of your query.
然后在选择查询时调用您的函数。