更新时间:2023-11-18 21:55:28
我真的不建议这样做,在应用程序层中有很多更好的方法可以做到这一点,但是以下内容避免了循环,并且不那么冗长比您目前的方法:
I wouldn't really advise it, there are much better ways of doing this in the application layer, but the following avoids loops, and is a lot less verbose than your current method:
CREATE PROCEDURE dbo.GetJSON @ObjectName VARCHAR(255), @registries_per_request smallint = null
AS
BEGIN
IF OBJECT_ID(@ObjectName) IS NULL
BEGIN
SELECT Json = '';
RETURN
END;
DECLARE @Top NVARCHAR(20) = CASE WHEN @registries_per_request IS NOT NULL
THEN 'TOP (' + CAST(@registries_per_request AS NVARCHAR) + ') '
ELSE ''
END;
DECLARE @SQL NVARCHAR(MAX) = N'SELECT ' + @Top + '* INTO ##T ' +
'FROM ' + @ObjectName;
EXECUTE SP_EXECUTESQL @SQL;
DECLARE @X NVARCHAR(MAX) = '[' + (SELECT * FROM ##T FOR XML PATH('')) + ']';
SELECT @X = REPLACE(@X, '<' + Name + '>',
CASE WHEN ROW_NUMBER() OVER(ORDER BY Column_ID) = 1 THEN '{'
ELSE '' END + Name + ':'),
@X = REPLACE(@X, '</' + Name + '>', ','),
@X = REPLACE(@X, ',{', '}, {'),
@X = REPLACE(@X, ',]', '}]')
FROM sys.columns
WHERE [Object_ID] = OBJECT_ID(@ObjectName)
ORDER BY Column_ID;
DROP TABLE ##T;
SELECT Json = @X;
END
我将您的两部分对象名称(@schema和@table)更改为仅接受完整的对象名称.
N.B. I've changed your two part object name (@schema and @table) to just accept the full object name.
Example on SQL Fiddle
想法是基本上使用SQL-Server中的XML扩展将表转换为XML,然后只需将开始标记替换为{ColumnName:
,将结束标记替换为,
.然后,它需要再进行两次替换,才能停止在每行的最后一列中添加右括号,并从JSON字符串中删除最后一个,
.
The idea is to basically use the XML extension within SQL-Server to turn the table into XML, then just replace the start tags with {ColumnName:
and the end tags with ,
. It then requires two more replaces to stop add the closing bracket to the last column of each row, and the remove the final ,
from the JSON string.