首页 > 代码库 > 在SQL 中生成JSON数据
在SQL 中生成JSON数据
这段时间接手一个数据操作记录的功能，刚拿到手上的时候打算用EF做，后来经过仔细考虑最后还是觉定放弃，最后思考再三决定：
1、以模块为单位分表、列固定(其实可以所有的操作记录都放到同一个表，但是考虑到数据量大的时候查询性能的问题还是分表吧)列：主键ID、引用记录主键ID、操作时间、操作类型、详细信息(里面存储的就是序列化后的值)
2、在客服端解析保存的序列化的值
但是用xml还是用json呢，这有是一个问题，显然用xml在存储过程正很容易就能生成了:SELECT * FROM TABLE FOR XML AUTO 就ok了，
但是用xml的话，在客服端解析就麻烦些了，所以弃用了xml保存，而使用了最爱的json，现在的问题就是写一个通用的存储过程来生成json了，经过我1天多的拼写终于出炉了(其实难度不大，就是拼字符串容易出错)
/*=================获取JSON格式数据
@table_name varchar(100):表名
@Condition NVARCHAR(max)：条件(and 1=1)
@json varchar(max) OUTPUT:生成的JSON数据
@limit int =NULL ：取前多少条，为null 取所有
@ConvertColumns NVARCHAR(max)=null:要转换的列，建议填写非html的列，html列目前会有问题
==================================*/
CREATE PROC GetJSON(
@table_name varchar(100),
@Condition NVARCHAR(max)=‘‘,
@json varchar(max) OUTPUT,
@limit int =NULL,
@ConvertColumns NVARCHAR(max)=null
)
AS
declare @query varchar(max),
@table_schema varchar(max) = null
if(charindex(‘.‘, @table_name) > 0 )
begin
set @table_schema = replace(replace( substring(@table_name, 0, charindex(‘.‘,@table_name)), ‘[‘, ‘‘), ‘]‘, ‘‘)
set @table_name = replace(replace( substring(@table_name, charindex(‘.‘,@table_name) + 1,len(@table_name)), ‘[‘, ‘‘), ‘]‘, ‘‘)
END
IF EXISTS ( SELECT 1 FROM tempdb..sysobjects WHERE id = OBJECT_ID(‘tempdb..#tmpJsonTable‘) )
DROP TABLE #tmpJsonTable
CREATE 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(cast([‘ + column_name + ‘] as varchar(max)),‘‘\‘‘,‘‘\\‘‘),‘‘"‘‘,‘‘\"‘‘),char(10),‘‘\n‘‘),char(13),‘‘\n‘‘) ‘ 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(cast([‘ + column_name + ‘] as varchar(max)),‘‘\‘‘,‘‘\\‘‘),‘‘"‘‘,‘‘\"‘‘),char(10),‘‘\n‘‘),char(13),‘‘\n‘‘) + ‘‘‘ end +
--处理后缀"
case WHEN data_type=‘uniqueidentifier‘ or data_type like ‘%char%‘ or data_type like ‘%text%‘ then ‘"‘ else ‘‘ end end + ‘,‘
AS [text()]
from information_schema.columns
where table_name = @table_name
--AND (COLUMN_NAME=@ColumnName AND ‘[‘+COLUMN_NAME+‘]‘ IN (SELECT MyValues FROM dbo.funSplitArray(@ColumnValues,‘,‘)))
AND(@ConvertColumns IS NULL OR COLUMN_NAME IN(SELECT MyValues FROM dbo.funSplitArray(@ConvertColumns,‘,‘)))
and (@table_schema is null or table_schema = @table_schema) FOR XML PATH(‘‘) ) as varchar(max)) +
‘‘‘),1,1,‘‘‘‘)) + ‘‘ }‘‘ as json from ‘ + @table_name + ‘ with(nolock) where 1=1 ‘+@Condition
exec sp_sqlexec @query
SELECT @query
set @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(‘‘)) AS varchar(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页面上也不需要用每个地方都去解析了....
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。