从SQL 2008开始,SQL Sever已经支持JSON数据,SQL 2016已经对JSON数据的处理支持非常完善。
对于SQL 2016以上版本的用户,可以直接调用原生方法,效率更高。
因客户采用SQL 2005,为了做到通用,特地写了以下存储过程,效率不差。
CREATE PROCEDURE[dbo].[GetJqgridJson](
@ParameterSQL AS NVARCHAR(MAX), --SQL SELECT语句(可加条件)
@page INT = 1, --页码
@rows int = 10, --每页记录数
@soid nvarchar(100) = N'idSort' --排序字段
)
AS
--作用:将查询记录转换为JQGRID的JSON字符串,兼容SQL 2005。
--建议:SQL2008开始已经对JSON的原生支持,SQL2016对JSON支持已经完善。建议高版本的SQL采用原生的支持
--调用方法:
----EXEC dbo.GetJqgridJson 'select * from table where 条件 ......', 页码, 每页记录数, '排序字段'
--返回 json字段
--限制:@ParameterSQL中不应该存在Json_Row和idSort字段,字段值中不能包含'<[":TmplateString:"]>'字符
如果只是为了将记录转换为通用JSON,可以稍微修改下the_End:标号下面的处理过程,入参也可以自行调整。
存储过程中采用了动态SQL生成全局临时表,用于数据结构的抽取。
采用按数据行处理数据,提高处理效率,特别适合于小数据量的处理(如为JQGrid提供后台JSON数据)
CREATE PROCEDURE[dbo].[GetJqgridJson](
@ParameterSQL AS NVARCHAR(MAX), --SQL SELECT语句(可加条件)
@page INT = 1, --页码
@rows INT = 10, --每页记录数
@soid nvarchar(100) = N'idSort' --排序字段
)
AS
--作用:将查询记录转换为JQGRID的JSON字符串,兼容SQL 2005。
--建议:SQL2008开始已经对JSON的原生支持,SQL2016对JSON支持已经完善。建议高版本的SQL采用原生的支持
--调用方法:
----EXEC dbo.GetJqgridJson 'select * from table where 条件 ......', 页码, 每页记录数, '排序字段'
--返回 json字段
--限制:@ParameterSQL中不应该存在Json_Row和idSort字段,字段值中不能包含'<[":TmplateString:"]>'字符
BEGIN
SET NOCOUNT ON
--CEILING(@rowcount/@rows)得到总页数,但两数相除必须起码其中一个为非整数方可得小数,两INT相除得向下取整的整数
declare @rowcount decimal(10,0)
declare @current_row int = 1
DECLARE @JSON NVARCHAR(MAX) = ''
DECLARE @SQL NVARCHAR(MAX)
--取得总记录数
SET @Sql = 'SET @rowcount = (SELECT COUNT(*) FROM (' + @ParameterSQL + ') A)'
EXEC SP_EXECUTESQL @Sql, N'@rowcount INT OUTPUT', @rowcount OUTPUT
IF @rowcount = 0
BEGIN
GOTO the_End
END
DECLARE @total INT
DECLARE @records INT --实际获取的记录数
SET @total = CEILING(@rowcount/@rows)
IF @page < @total
SET @records = @rows
ELSE
BEGIN
SET @page = @total --页码不能超过总页数
SET @records = @rowcount - (@page - 1)*@rows --末页的记录数
END
DECLARE @XMLString NVARCHAR(MAX)
DECLARE @XML XML
DECLARE @StartRoot VARCHAR(100);SET @StartRoot='<Json_Row>'
DECLARE @EndRoot VARCHAR(100);SET @EndRoot='</Json_Row>'
SET @SQL = 'SET @XML = (select * from ' +
'(select ROW_NUMBER() over(order by t.[' + @soid + ']) as idSort, * from (' + @ParameterSQL + ') as t) A ' +
'where A.idSort between ' + convert(varchar(10),(@page - 1) *@rows + 1) + ' and ' + convert(varchar(10), @page*@rows)
+' FOR XML PATH(''Json_Row''))'
--PRINT @SQL
EXEC sp_executesql @SQL, N'@XML XML OUTPUT', @XML OUTPUT
SET @XMLString=CAST(@XML AS NVARCHAR(MAX))
DECLARE @Row NVARCHAR(MAX)
DECLARE @RowTmp NVARCHAR(MAX)
DECLARE @RowStart INT
DECLARE @RowEnd INT
DECLARE @FieldStart INT = 0
DECLARE @FieldEnd INT = 0
DECLARE @KEY NVARCHAR(MAX)
DECLARE @Value NVARCHAR(MAX)
DECLARE @StartField NVARCHAR(100);SET @StartField='<'
DECLARE @EndField NVARCHAR(100);SET @EndField='>'
DECLARE @TemplateString VARCHAR(100) = '<[":TmplateString:"]>'
--提取数据结构
DECLARE @RndTableName VARCHAR(50) --随机临时表名
SET @RndTableName = '##' + REPLACE(CONVERT(VARCHAR(50),NEWID()), '-','')
--if object_id('tempdb..##' + @RndTableName) is not null
-- drop table ##t1
SET @SQL = 'SELECT * INTO ' + @RndTableName + ' FROM (SELECT * FROM (' + @ParameterSQL + ') A WHERE 1=0) B'
EXEC(@SQL)
DECLARE @t2 TABLE(
colname nvarchar(100),
datatype varchar(100)
)
--INSERT @t2 values('idSort', 'int')
INSERT INTO @t2 SELECT c.name COLUMN_NAME, t.name DATA_TYPE
FROM tempdb.dbo.syscolumns c, tempdb.dbo.systypes t
WHERE c.xusertype = t.xusertype
AND c.id = object_id('tempdb..' + @RndTableName)
--删除临时表
SET @SQL = 'drop table ' + @RndTableName
EXEC (@SQL)
declare @colname nvarchar(100)
declare @datatype varchar(100)
SET @RowStart=CharIndex(@StartRoot,@XMLString,0)
DECLARE cur1 cursor SCROLL local /* 声明游标,默认为 FORWARD_ONLY 游标 */
FOR SELECT colname, datatype FROM @t2
OPEN cur1 /* 打开游标 */
while @current_row <= @records
BEGIN
SET @RowStart=@RowStart+Len(@StartRoot)
SET @RowEnd=CharIndex(@EndRoot,@XMLString,@RowStart)
SET @Row=SubString(@XMLString,@RowStart,@RowEnd-@RowStart)
SET @JSON=@JSON+'{'
-- for each row
--移除idSort
SET @FieldStart = CharIndex('<idSort>', @Row, 0) + LEN('<idSort>')
SET @FieldEnd = CharIndex('</idSort>', @Row, @FieldStart)
SET @Value = SubString(@Row, @FieldStart , @FieldEnd-@FieldStart)
SET @Row = replace(@Row, '<idSort>' + @Value + '</idSort>', '')
FETCH FIRST from cur1 into @colname, @datatype /* 读取第 1 行数据 */
WHILE @@FETCH_STATUS = 0 /* 用 WHILE 循环控制游标活动 */
BEGIN
--先替换空元素
SET @RowTmp = @Row
SET @Row = replace(@Row, '<' + @colname + '/>', '"' + @colname + '":' + '"",')
IF @RowTmp = @Row
BEGIN
SET @Value = ''
--替换有值元素
SET @FieldStart = CharIndex('<' + @colname+ '>', @Row, 0) + LEN('<' + @colname + '>')
SET @FieldEnd = CharIndex('</' + @colname + '>', @Row, @FieldStart)
--取值
IF @FieldEnd > @FieldStart
BEGIN
SET @Value = SubString(@Row, @FieldStart , @FieldEnd-@FieldStart)
--临时用模板值代替
SET @Row = replace(@Row, '<' + @colname + '>' + @Value + '</' + @colname + '>', @TemplateString)
--处理各种不同类型的值
IF (@datatype='date' OR @datatype='datetime')
SET @Value = replace(@Value, 'T', ' ')
--TODO: 处理其他数据类型?
ELSE
BEGIN
--处理值中的特殊字符
SET @Value = replace(@Value, '\', '\\') --\
SET @Value = replace(@Value, '''', '\''') --'
SET @Value = replace(@Value, '"', '\"') --"
SET @Value = replace(@Value, '&', '\&') --&
SET @Value = replace(@Value, char(9), '\t') --制表
SET @Value = replace(@Value, char(10), '\n') --换行
SET @Value = replace(@Value, char(13), '\r') --回车
SET @Value = replace(@Value, char(8), '\b') --退格
SET @Value = replace(@Value, char(12), '\f') --换页
--TODO: 增加其他JS控制符号?
END
--模板值@TemplateString替换为原值
SET @Row = replace(@Row, @TemplateString , '"' + @colname + '":"' + rtrim(ltrim(@Value)) + '",')
END
END
FETCH NEXT from cur1 into @colname, @datatype /*读取其它记录 */
END --一行处理完毕
SET @JSON= @JSON + @Row + '},'
SET @current_row = @current_row + 1
SET @RowStart=CharIndex(@StartRoot,@XMLString,@RowEnd)
END
CLOSE cur1
DEALLOCATE cur1
IF LEN(@JSON)>0 SET @JSON=SubString(@JSON,0,LEN(@JSON))
--JQGRID READER FORMAT
--jsonReader :
--{
-- root: "rows", //数据模型
-- page: "page",//数据页码
-- total: "total",//数据总页码
-- records: "records",//数据总记录数
--}
the_End:
SET @SQL = '"page":"' + convert(varchar(10), @page) + '",'
SET @SQL = @SQL + '"total":"' + convert(varchar(10), @total) + '",'
SET @SQL = @SQL + '"records":"' + convert(varchar(10), @rowcount) + '",'
SET @JSON = '"rows":[' + @JSON + ']'
SET @JSON = '{' + @SQL + @JSON + '}'
SET @JSON = REPLACE(@JSON, '",}', '"}')
SELECT @JSON AS json
END