none
How to create a function to output Any recordset into JSON format?

    Question

  • I want to create a T-SQL function,to output "ANY" recordset into JSON format.That means,this function needs two parameters,the first is the recordset and the second is output json parametername.For example,I got a recordset with three columns("fieldA","fieldB","fieldC") and two rows("1","2","3";"a","b","c"),thus the function should output the json string like "datalist":[{"fieldA":"1","fieldB":"2","fieldC":"3"},{"fieldA":"a","fieldB":"b","fieldC":"c"}]("datalist" is the parametername).

    Now the question:

    1,How to pass a recordset as a parameter to a function?especially the recordset does NOT have a certain structure.

    2,How could I get the structure(fields and their datatypes) from any recordset Such as a select sql?

    Thanks

    Friday, December 27, 2013 5:30 AM

Answers

All replies

  • 1 - you can't.  

    2 - this, you can do. 

    select *

    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_CATALOG='myTable'

    will return the schema for myTable in the currently selected DB.

    If you're creating JSON, I assume you've got a web service or something between the browser and SQL Server.  That's the place to create your JSON.



    Christian Graus

    Friday, December 27, 2013 6:36 AM
  • I have solved The first question by pass the query string instead of the recordset.

    now how can I get the fieldnames and the datatypes form "ANY" result dataset?NOT from a table.

    Friday, December 27, 2013 7:27 AM
  • I doubt you can, because there's no where it's stored, apart from the table I pointed you to.  You can probably do it by combining the method you're using, to parse the string and work out column and table names from there.  But, of course, it's messy and ugly.  Why are you doing this ?


    Christian Graus

    Friday, December 27, 2013 7:43 AM
  • You need to put the resultset in a temporary table for that and then use catalog views to get column information from it.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, December 27, 2013 7:56 AM
  • Why do you need to return JSON string from SQL Server? It is much easier to return a result set and create JSON in .NET.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, December 27, 2013 6:45 PM
    Moderator
  • Hi ,

    Here is a really good example to implement the JSON parser and outputter. Can check here ==>

    https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/


    Regards Harsh

    Saturday, December 28, 2013 4:16 AM
  • Hi,

    The example is wonderful,but I have found my way by using Procedure and temp tables(local and global).

    Here is my code and any optimization is welcome:

    ALTER PROCEDURE [dbo].[SDIO_Common_Query_To_JSON](@QueryStr NVarchar(4000),@JsonParameterName Varchar(50))
    AS
    BEGIN
        Declare @Enter Char(2) = Char(13) + Char(10)
        Declare @SQL NVarchar(Max) = 'if OBJECT_ID(N''Tempdb.dbo.#'') Is Not Null'
        Set @SQL = @SQL + @Enter + 'begin'
        Set @SQL = @SQL + @Enter + '    drop table Tempdb.dbo.#'
        Set @SQL = @SQL + @Enter + 'end'
        Set @SQL = @SQL + @Enter + @QueryStr
        Set @SQL = @SQL + @Enter + 'Declare @SDIO_Query_To_Json_S1 varchar(Max) = '''',@SDIO_Query_To_Json_S2 Nvarchar(400) = '''',@SDIO_Query_To_Json_S3 Nvarchar(4000) = '''''
        Set @SQL = @SQL + @Enter + 'Declare @SDIO_Query_To_Json_V1 varchar(50),@SDIO_Query_To_Json_V2 varchar(55),@SDIO_Query_To_Json_V3 varchar(100)'
        Set @SQL = @SQL + @Enter + 'Declare @Enter Char(2) = Char(13) + Char(10)'
        Set @SQL = @SQL + @Enter + 'Declare SDIO_Query_To_Json_DT Cursor For select lower(a.name),'',@'' + a.name,''Declare @'' + a.name + '' '' + case a.precision when 0 then case a.is_ansi_padded when 1 then convert(nvarchar(15),b.name+ ''(''+convert(nvarchar(10),a.max_length / 2)+'')'') when 0 then b.name end else case a.scale when 0 then b.name else b.name+ ''(''+convert(nvarchar(10),a.precision)+'',''+convert(nvarchar(10),a.scale)+'')'' end end from Tempdb.sys.columns a left join Tempdb.sys.types b on a.system_type_id=b.system_type_id and a.user_type_id=b.user_type_id where a.object_id = OBJECT_ID(N''tempdb.dbo.#'')'
        Set @SQL = @SQL + @Enter + 'Open SDIO_Query_To_Json_DT'
        Set @SQL = @SQL + @Enter + 'Fetch Next From SDIO_Query_To_Json_DT Into @SDIO_Query_To_Json_V1,@SDIO_Query_To_Json_V2,@SDIO_Query_To_Json_V3'
        Set @SQL = @SQL + @Enter + 'While (@@FETCH_STATUS = 0)'
        Set @SQL = @SQL + @Enter + 'Begin'
        Set @SQL = @SQL + @Enter + '    Set @SDIO_Query_To_Json_S3 = @SDIO_Query_To_Json_S3 + @Enter + @SDIO_Query_To_Json_V3'
        Set @SQL = @SQL + @Enter + '    Set @SDIO_Query_To_Json_S2 = @SDIO_Query_To_Json_S2 + @SDIO_Query_To_Json_V2'
        Set @SQL = @SQL + @Enter + '    Set @SDIO_Query_To_Json_S1 = @SDIO_Query_To_Json_S1 + '',"'' + @SDIO_Query_To_Json_V1 + ''":"'''' + DBO.JSONEscaped(IsNull(CONVERT(Nvarchar(4000),@'' + @SDIO_Query_To_Json_V1 + '',20),'''''''')) + ''''"'''
        Set @SQL = @SQL + @Enter + '    Fetch Next From SDIO_Query_To_Json_DT Into @SDIO_Query_To_Json_V1,@SDIO_Query_To_Json_V2,@SDIO_Query_To_Json_V3'
        Set @SQL = @SQL + @Enter + 'End'
        Set @SQL = @SQL + @Enter + 'Set @SDIO_Query_To_Json_S2 = ''Fetch Next From SDIO_Common_CreateJsonFromTable_DT Into '' + stuff(@SDIO_Query_To_Json_S2,1,1,'''')'
        Set @SQL = @SQL + @Enter + 'Set @SDIO_Query_To_Json_S1 = ''Set @JsonStr = @JsonStr + '''',{'' + stuff(@SDIO_Query_To_Json_S1,1,1,'''') + ''}'''''''
        Set @SQL = @SQL + @Enter + 'Close SDIO_Query_To_Json_DT'
        Set @SQL = @SQL + @Enter + 'Deallocate SDIO_Query_To_Json_DT'
        Set @SQL = @SQL + @Enter + 'Declare @SQLStr Nvarchar(Max) = ''Declare @Enter Char(2) = Char(13) + Char(10)'''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''Declare @JsonStr NVarchar(Max) = '''''''''''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''Declare SDIO_Common_CreateJsonFromTable_DT Cursor For Select * From tempdb.dbo.#'''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + @SDIO_Query_To_Json_S3'
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''Open SDIO_Common_CreateJsonFromTable_DT'''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + @SDIO_Query_To_Json_S2'
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''While (@@FETCH_STATUS = 0)'''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''Begin'''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''    '' + @SDIO_Query_To_Json_S1'
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''    '' + @SDIO_Query_To_Json_S2'
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''End'''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''Set @JsonStr = stuff(@JsonStr,1,1,'''''''')'''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''Close SDIO_Common_CreateJsonFromTable_DT'''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''Deallocate SDIO_Common_CreateJsonFromTable_DT'''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''drop table Tempdb.dbo.#'''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''--Print @JsonStr'''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''If OBJECT_ID(N''''Tempdb.dbo.##'''') Is Not Null'''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''Begin'''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''    drop table Tempdb.dbo.##'''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''End'''
        Set @SQL = @SQL + @Enter + 'Set @SQLStr = @SQLStr + @Enter + ''Select '''',"' + @JsonParameterName + '":['''' + IsNull(@JsonStr,'''''''') + '''']'''' as Json Into ##'''
        Set @SQL = @SQL + @Enter + '--Print @SQLStr'
        Set @SQL = @SQL + @Enter + 'exec sp_executesql @SQLStr'
        --Print @SQL
        exec sp_executesql @SQL
        if OBJECT_ID(N'tempdb.dbo.#') Is Not Null
        begin
            drop table Tempdb.dbo.#
        end
    END

    Thanks again.

    Tuesday, December 31, 2013 2:58 AM
  • ALTER FUNCTION [dbo].[JSONEscaped] ( /* this is a simple utility function that takes a SQL String with all its clobber and outputs it as a sting with all the JSON escape sequences in it.*/

      @Unescaped NVARCHAR(MAX) --a string with maybe characters that will break json

      )

    RETURNS NVARCHAR(MAX)

    AS

    BEGIN

      SELECT  @Unescaped = REPLACE(@Unescaped, FROMString, TOString)

      FROM    (SELECT

                '\"' AS FromString, '"' AS ToString

               UNION ALL SELECT '\', '\\'

               UNION ALL SELECT '/', '\/'

               UNION ALL SELECT  CHAR(08),'\b'

               UNION ALL SELECT  CHAR(12),'\f'

               UNION ALL SELECT  CHAR(10),'\n'

               UNION ALL SELECT  CHAR(13),'\r'

               UNION ALL SELECT  CHAR(09),'\t'

              ) substitutions

    RETURN @Unescaped

    END
    Tuesday, December 31, 2013 3:00 AM
  • There's really no reason to do this in a stored  proc.  You may as well string mash your SQL in code.  

    Christian Graus My SQL articles are available starting at http://www.codeproject.com/Articles/700317/SQL-Wizardry-Episode-One-Joins

    Tuesday, December 31, 2013 3:33 AM
  • I'm working as an Android APP Object director,Thus I must pay attention on 3 parts:

    1,App Programing

    2,App data interface/gateway Programing

    3,database Programing

    I'm trying to cut one or more of them,first the 2nd part by creating a standard I/O interface that only pass parameters and jsons between APP and database,that means the database should handle all the data missions and output jsons.

    After this,Maybe I'll go even further to cut the 1st part by using standard views.Finally I will only have to maintenance the database itself.

    Tuesday, December 31, 2013 3:53 AM