将SQL Server的任意记录转换为JSON格式（JQGRID） -- 支持SQL 2005

从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