Generating JSON data in SQL

This period of time to take over a function of data operation Record, just get the hands of the time to do with EF, and then after careful consideration of the final or feel to give up, the final thinking repeatedly decided:

1, the module for the Unit table, column fixed (in fact, all the operation records can be placed in the same table, but considering the large amount of data when the query performance problem or the table bar) column: Primary key ID, reference record primary key ID, operation time, operation type, details (stored in the serialized value)

2, in the customer service side to resolve the stored serialized value

But with XML or JSON, this is a problem, and it is obvious that XML can be easily generated in a stored procedure:SELECT * from TABLE for XML AUTO is OK,

But with XML, in the customer service side of the trouble to parse, so discard the XML save, and use the favorite JSON, now the problem is to write a common stored procedure to generate JSON, after my 1 days more spelling finally baked (in fact, the difficulty is not very difficult, that is, spelling string error)

/*================= get JSON format data   @table_name varchar (100): Table name   @Condition NVARCHAR (max): condition (and 1=1)  @ JSON varchar (max) OUTPUT: Generated JSON data     @limit int =null: How many bars to take, null for all   @ConvertColumns NVARCHAR (max) = NULL: The column to be converted, it is recommended to fill in non-HTML columns, HTML column currently has a problem ==================================*/create PROC Getjson (  @table_name varchar,  @Condition NVARCHAR (max) = ",  @json varchar (max) output,    @limit int =null, @ Convertcolumns NVARCHAR (max) =null) asdeclare @query varchar (max),  @table_schema varchar (max) = Nullif (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)), ' [', ' '), '] ', ' ENDIF 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 are not null and then ' top ' + cast (@limit as varchar) + ' Else ' end + ' ' {' + REVERSE (STUFF (REVERSE (' "' +    CAST ((select '" ' + column_name + ' ": ' + ----handling NULL issues   CA SE when is_nullable = ' YES ' and ' + case when [' + ' + column_name + '] are null then ' null ' ' Else ' +   --processing prefix '     case where data_type= ' uniqueidentifier ' or data_type like '%date% ' or data_type like '%char% ' or data_type lik E '%text% ' Then ' "'" ' + ' Else ' end +   /* Type conversion */    case when data_type like '%date% ' then ' conver T (varchar), [' + column_name + '], + ' "'" '      else ' replace (replace (replace (cast ([' + ' + colum N_name + '] as varchar (max)), ' \ ', ' \ \ '), ' ' ' ', ' ' ' ' ', ' ' "', char ', ' \ n '), char ($), ' \ n ') ' End +   --processing suffix '     Case Data_type= ' uniqueidentifier ' or data_type like '%char% ' or data_type '%text% ' and ' then ' + ' ' ' ' ' Else ' end + ' end + ' &NB Sp else   --processing prefix "   case when data_type= ' uniqueidentifier ' or data_type like '%date% ' or data_type like ' %char% ' or data_type like '%text% ' then ' ' ' Else ' ' end + ' ' + ' +   /* type conversion */   case when Data_type l Ike '%date% ' then ' convert (varchar), [' + column_name + '], + ' "'" '     Else ' replace (replace (repla CE (CAST ([' + column_name + '] as varchar (max)), ' \ ', ' \ \ '), ' "', '" ' "'), char (ten), ' \ n '), char (+), ' \ n ') + ' end +    --processing suffix "   case when data_type= ' uniqueidentifier ' or data_type like '%char% ' or data_type like '%tex t% ' Then ' ' Else ' end end + ', '   as [text ()]      from information_schema.columns  where table _name = @table_name    --and ([email protected] and ' [' +column_name+ '] ' in (SELECT myvalues from DBO.FUNSPL Itarray (@ColumnValues, ', ')))    and (@ConvertColumns is NULL OR column_name in (the SELECT myvalues from Dbo.funsplitarray (@ConvertColumns, ', ')))    and (@table_schema is null or TABLE_SCHEMA = @table_schema) for XML PATH (")) as varchar (max)) +        "), (") + "}" as json from ' + @table_name + ' with (nolock) where 1=1 ' [Email protec Ted]exec sp_sqlexec @querySELECT @queryset @json =   --' {' + char ' + char (9) +   --' "RecordCount" : ' + Cast ((select COUNT (*) from #tmpJsonTable) as varchar (+)) + ', ' + char (Ten) + char (9) +   --' "Records": ' + char ' + char (9) + char (9) +  ' [' + char '     + REVERSE (STUFF (REVERSE ((SELECT char (9) + char (9) + JSON + ', ' + char (ten) as [text ()] from #tmpJsonTable for XML PATH (")) as varchar (max)),") ")     + char (1 0) + char (9) + char (9) + '] '  --+ char (Ten) + '} ' drop table #tmpJsonTable   --declare @table_name varchar (100 ) = ' SalesOrder ',--@ConvertCOlumns NVARCHAR (max) = ' Salesorderno,salesorderid ',--@Condition NVARCHAR (max) = ',--@json varchar (max)  --exec Getjson @[email protected]_name,@[email protected],@[email protected],@[email protected] Output--select @json Call this stored procedure to get the serialized JSON data using the output parameters. At this point, the difference in the customer service side analysis. I'm going to write a method to parse dynamically based on the JSON object parameters passed in, so there's no need to parse the ....  in every place on the UI page.