How can I get a JSON object from a SQL Server table?

Question

I have a view that I want to be converted into JSON. What is the SQL that I can use to produce on the server the JSON string needed to be returned?

Answer 1

-- 
-- Author:      Thiago R. Santos                                           --
-- Create date: Aug 3rd 2008                                                   --
-- Description: Returns the contents of a given table                      --
--              in JavaScript Object Notation.                             --
-- Params:                                                                 --
--      @table_name: the table to execute the query                        --
--      @registries_per_request: equivalent to "select top N * from table" 
-- 
--                               replcing N by the actual number           
-- Influenced by Thomas Frank's post MySQL to JSON @ January 23, 2007      --
-- Post Url: http://www.thomasfrank.se/mysql_to_json.html                  --



create procedure [dbo].[GetJSON]
(
@table_name varchar(50),
@registries_per_request smallint = null
)
as
begin
if((select count(*) from information_schema.tables where table_name =   @table_name)     > 0)
begin
    declare @json varchar(max),
            @line varchar(max),
            @columns varchar(max),
            @sql nvarchar(max),
            @columnNavigator varchar(50),
            @counter tinyint,
            @size varchar(10)

    if (@registries_per_request is null) 
    begin
        set @size = ''
    end
    else 
    begin
        set @size = 'top ' + convert(varchar, @registries_per_request)
    end
    set @columns = '{'

    declare schemaCursor cursor
    for select column_name from information_schema.columns where table_name = @table_name
    open    schemaCursor    

    fetch next from schemaCursor
    into  @columnNavigator

    select  @counter = count(*) from information_schema.columns where table_name = @table_name

    while @@fetch_status = 0
    begin
        set @columns = @columns + '''''' + @columnNavigator + ''''':'''''' + convert(varchar, ' + @columnNavigator + ') + '''''''
        set @counter = @counter - 1
        if(0 != @counter) 
        begin
            set @columns = @columns + ','
        end

        fetch next from schemaCursor
        into  @columnNavigator
    end 

    set @columns =  @columns + '}'

    close       schemaCursor
    deallocate  schemaCursor

    set @json = '['

    set @sql = 'select  ' + @size + '''' + @columns + ''' as json into tmpJsonTable from ' + @table_name
    exec sp_sqlexec @sql

    select  @counter = count(*) from tmpJsonTable

    declare tmpCur cursor
    for     select * from tmpJsonTable
    open    tmpCur

    fetch next from tmpCur
    into  @line

    while @@fetch_status = 0
    begin
        set @counter = @counter - 1
        set @json = @json + @line
        if ( 0 != @counter ) 
        begin
            set @json = @json + ','
        end

        fetch next from tmpCur
        into  @line
    end

    set @json = @json + ']'

    close       tmpCur
    deallocate  tmpCur
    drop table  tmpJsonTable

    select @json as json
end
end