2023年7月13日发(作者：)

在SQL中⽣成JSON数据这段时间接⼿⼀个数据操作记录的功能，刚拿到⼿上的时候打算⽤EF做，后来经过仔细考虑最后还是觉定放弃，最后思考再三决定：1、以模块为单位分表、列固定(其实可以所有的操作记录都放到同⼀个表，但是考虑到数据量⼤的时候查询性能的问题还是分表吧)列：主键ID、引⽤记录主键ID、操作时间、操作类型、详细信息(⾥⾯存储的就是序列化后的值)2、在客服端解析保存的序列化的值但是⽤xml还是⽤json呢，这有是⼀个问题，显然⽤xml在存储过程正很容易就能⽣成了:SELECT * FROM TABLE FOR XML AUTO 就ok了，但是⽤xml的话，在客服端解析就⿇烦些了，所以弃⽤了xml保存，⽽使⽤了最爱的json，现在的问题就是写⼀个通⽤的存储过程来⽣成json了，经过我1天多的拼写终于出炉了(其实难度不⼤，就是拼字符串容易出错)/*=================获取JSON格式数据@TableName varchar(100):表名@ColumnIn nvarchar(100):列名，⽤于判断,@ColumnValues nvarchar(max)：值'aaa,bbb,ccc',@Condition NVARCHAR(max)：条件(and 1=1)@Json varchar(max) OUTPUT:⽣成的JSON数据@Limit int =NULL ：取前多少条，为null 取所有@ConvertColumns NVARCHAR(max)=null:要转换的列(⽬前已经排除为nvarchar(max)的列)==================================*/create PROC uspGetJSON(@TableName varchar(100),@ColumnIn nvarchar(100)='',@ColumnValues nvarchar(max),@Condition NVARCHAR(max)='',@Json varchar(max) OUTPUT,@Limit int =NULL,@ConvertColumns NVARCHAR(max)=null--@SortColumn nvarchar(100)=null,--@SortType nvarchar(10)='asc')AS--if(@SortColumn is null)-- set @SortColumn='LastModifiedDate'declare @query varchar(max),@table_schema varchar(max) = nullif(charindex('.', @TableName) > 0 )beginset @table_schema = replace(replace( substring(@TableName, 0, charindex('.',@TableName)), '[', ''), ']', '')set @TableName = replace(replace( substring(@TableName, charindex('.',@TableName) + 1,len(@TableName)), '[', ''), ']', '')ENDIF EXISTS ( SELECT 1 FROM tempdb..sysobjects WHERE id = OBJECT_ID('tempdb..#tmpJsonTable') )

DROP TABLE #tmpJsonTableCREATE TABLE #tmpJsonTable(json NVARCHAR(max) NULL)--"dd"set @query =

'insert into #tmpJsonTable select ' + case when @Limit is not null then 'top ' + cast(@Limit as varchar(32)) + ' ' else '' end + '''{ '' +REVERSE(STUFF(REVERSE(''' +CAST((SELECT ' "' + column_name + '" : ' +

----处理为null的问题case WHEN is_nullable = 'YES' then ''' + case when [' + column_name + '] is null then ''null'' else ' +

--处理前缀"case WHEN data_type='uniqueidentifier'or data_type like '%date%' or data_type like '%char%' or data_type like '%text%' then '''"'' + ' else '' end +

/*类型转换*/case WHEN data_type like '%date%' then 'convert(varchar(23),[' + column_name + '], 20)+ ''"'''

else 'replace(replace(replace(replace(replace(cast([' + column_name + '] as varchar(max)),'''',''''),''"'',''"''),char(10),''n''),char(13),''n''),char(9),''t'') ' end+

--处理后缀"case WHEN data_type='uniqueidentifier' or data_type like '%char%' or data_type like '%text%' then '+ ''"''' else '' end + ' end + '''ELSE--处理前缀"

case WHEN data_type='uniqueidentifier' or data_type like '%date%' or data_type like '%char%' or data_type like '%text%' then '"' else '' end + ''' + ' +/*类型转换*/case WHEN data_type like '%date%' then 'convert(varchar(23),[' + column_name + '], 20)+ '''else 'replace(replace(replace(replace(replace(cast([' + column_name + '] as varchar(max)),'''',''''),''"'',''"''),char(10),''n''),char(13),''n''),char(9),''t'') + '''end +--处理后缀"case WHEN data_type='uniqueidentifier'OR data_type LIKE 'date%' or data_type like '%char%' or data_type like '%text%' then '"' else '' end end + ','AS [text()]

from information_s

where table_name = @TableName and (character_maximum_length IS NULL OR character_maximum_length!=-1)AND(@ConvertColumns IS NULL OR COLUMN_NAME IN(SELECT MyValues FROM itArray(@ConvertColumns,',')))and (@table_schema is null or table_schema = @table_schema) FOR XML PATH('') ) as varchar(max)) +'''),1,1,'''')) + '' }'' as json from ' + @TableName + ' with(nolock) where ('+@ColumnIn+' in (Select MyValues itArray('''+@ColumnValues+''','',''))) '+@Condition--+' ORDER BY '+@SortColumn+' '+@SortTypeexec sp_sqlexec @query--SELECT @queryset @Json =

--'{' + char(10) + char(9) +--'"recordCount" : ' + Cast((select count(*) from #tmpJsonTable) as varchar(32)) + ',' + char(10) + char(9) +--'"records" : ' + char(10) + char(9) + char(9) +

'[' + char(10)+ REVERSE(STUFF(REVERSE(CAST((SELECT char(9) + char(9) + json + ',' + char(10) AS [text()] FROM #tmpJsonTable FOR XML PATH('')) ASvarchar(max))),1,2,''))+ char(10) + char(9) + char(9) + ']'-- + char(10) + '}'drop table #tmpJsonTable

--DECLARE @table_name varchar(100)='SalesOrder',-- @ConvertColumns NVARCHAR(max)='SalesOrderNo,SalesOrderID',-- @Condition NVARCHAR(max)='',-- @json varchar(max)

--EXEC GetJSON @table_name=@table_name,@ConvertColumns=@ConvertColumns,@Condition=@Condition,@json=@json OUTPUT--SELECT @json调⽤这个存储过程使⽤输出参数就可以得到序列化后的json数据了。⾄此，就差在客服端解析了。打算写个⽅法根据传递过来的json对象参数来动态解析，这样 ui页⾯上也不需要⽤每个地⽅都去解析了....

--⽤到的⾃定义函数CREATE function [dbo].[funSplitArray](@aString varchar(max),@pattern varchar(10))returns @temp table([Sid] [int] IDENTITY (1, 1) NOT NULL ,Myvalues varchar(100))--实现split功能的函数--说明：@aString，字符串，如“27,28,29”；@pattern，分隔标志，如“ ,”--使⽤select Myvalues FROM my_split(字符串, ',')as

begindeclare @i intset @aString=rtrim(ltrim(@aString))set @i=charindex(@pattern,@aString)while @i>=1begininsert @temp values(left(@aString,@i-1))set @aString=substring(@aString,@i+1,len(@aString)-@i)set @i=charindex(@pattern,@aString)endif @aString<>''

insert @temp values(@aString)return

end