Return json string from table data.

/****** 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