MSSQL table to json procedure
Return json string from table data.
— Author: Matthew D. Erwin (Snaptech, LLC)
— Create date: May 9, 2013
— Modify: Aiden Kim, May 30, 2014
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 |
/****** Object: StoredProcedure [dbo].[sp_table2json] Script Date: 07/07/2014 14:11:41 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- -- Author: Matthew D. Erwin (Snaptech, LLC) -- Create date: May 9, 2013 -- Description: Returns the contents of a given table -- in JavaScript Object Notation JSON - -- -- Very notably useful for generating MOCK .json files -- for testing OR before RESTful services are completed. -- -- This implementation: -- *removed cursor (using FOR XML PATH('')) -- *properly supports NULL vs quoted values -- *supports dates in ISO 8601 - presuming UTC -- *uses Data_Type AND Is_Nullable info -- *escapes '\' -- *formats output with tabs/newlines -- *can return final results AS XML to bypass -- truncation in SSMS -- *supports schema (e.g. [dbo].[TableName] -- *includes "recordCount" field -- Options: -- @table_name: the table to execute the query -- @limit: equivalent to "SELECT top N * FROM table" -- @ssms: flag to use if executing in Sql Server Management Studio -- to bypass result truncation limits. -- -- Inspired primarily by the 2008 work of Thiago R. Santos which was influenced by Thomas Frank. -- Usage: [dbo].[GetJSON] @Table_name = 'MySchema.MyTable', @limit = 50, @ssms = 0 -- -- -- Modify: Aiden Kim (Technoblood, JPN) -- Create date: May 30, 2014 -- Description: Multilingual(unicode) data supported -- Add timezone calculation for datetime type -- Change temp table to table variable => now, session safe -- Result change to OUTPUT parameters from result-set -- Remove SSMS -- CREATE PROCEDURE [dbo].[sp_table2json] ( @table_name NVARCHAR(max ), @condition NVARCHAR(max ) = null, @limit INT = NULL, @json NVARCHAR(MAX ) OUTPUT , @row_count INT OUTPUT )AS BEGIN DECLARE @query NVARCHAR (max), @table_schema NVARCHAR( max), @tz NVARCHAR(5 ) DECLARE @tmpJsonTable AS TABLE (json NVARCHAR (max)) SET @table_schema = NULL SET @row_count = 0 SET @json = N'[]' SET @tz = CASE WHEN (GETUTCDATE () = GETDATE()) THEN N 'Z' ELSE CASE WHEN (GETUTCDATE () < GETDATE()) THEN N '+' ELSE N'-' END + REPLACE (CONVERT( VARCHAR(5 ), DATEADD (minute, ABS(DATEDIFF (minute, GETUTCDATE(), GETDATE())), 0), 108 ),N':' ,N'' ) END 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 SET @query = N 'SELECT ' + CASE WHEN ( @limit IS NOT NULL AND @limit > 0) THEN 'TOP ' + CAST(@limit AS NVARCHAR (32)) + ' ' ELSE '' END + '''{ '' + REVERSE(STUFF(REVERSE(''' + CAST((SELECT ' "' + column_name + '" : ' + CASE WHEN ( is_nullable = 'YES') THEN ''' + CASE WHEN ([' + column_name + '] IS NULL) THEN ''NULL'' ELSE ' + CASE WHEN (data_type LIKE '%char%' OR data_type LIKE '%text%' ) THEN '''"'' + ' ELSE '' END + CASE WHEN (data_type LIKE '%date%' ) THEN 'CONVERT(NVARCHAR(23),[' + column_name + '], 126) + ''' + @tz + '''' ELSE 'REPLACE(REPLACE(REPLACE(REPLACE(CAST([' + column_name + '] AS NVARCHAR(max)),''\'',''\\''),''"'',''\"''),CHAR(10),''\n''),CHAR(13),''\n'') ' END + CASE WHEN (data_type LIKE '%char%' OR data_type LIKE '%text%' ) THEN '+ ''"''' ELSE '' END + ' END + ''' ELSE CASE WHEN (data_type LIKE '%char%' OR data_type LIKE '%text%' ) THEN '"' ELSE '' END + ''' + ' + CASE WHEN (data_type LIKE '%date%' ) THEN 'CONVERT(NVARCHAR(23),[' + column_name + '], 126) + ''' + @tz + '' ELSE 'REPLACE(REPLACE(REPLACE(REPLACE(CAST([' + column_name + '] AS NVARCHAR(max)),''\'',''\\''),''"'',''\"''),CHAR(10),''\n''),CHAR(13),''\n'') + ''' END + CASE WHEN (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 ( @table_schema IS NULL OR table_schema = @table_schema ) FOR XML PATH( '') ) AS NVARCHAR( max)) + '''),1,1,'''')) + '' }'' AS json FROM ' + @table_name + ' WITH(NOLOCK) ' + CASE WHEN (@condition IS NOT NULL AND @condition <> '') THEN N' WHERE ' + @condition ELSE '' END INSERT INTO @tmpJsonTable EXECUTE sp_executesql @query IF ( @@ERROR <> 0) RETURN - 1 -- Row Count SELECT @row_count = COUNT(*) FROM @tmpJsonTable IF ( @row_count > 0 ) BEGIN SET @json = '[' + CHAR( 10) + REVERSE (STUFF( REVERSE(CAST ((SELECT CHAR(9 ) + json + ',' + CHAR( 10) AS [text()] FROM @tmpJsonTable FOR XML PATH ('')) AS NVARCHAR(max ))),1, 2,'' )) + CHAR (10) + ']' END RETURN 0 END |
Print Friendly
