Cacher is the code snippet organizer for pro developers

-- 
-- 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"
-- 
--  							 replacing 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
-- 
-- Author: Mike Causer
-- Date: 2013-03-27
-- Modifications: added support for schemas
-- http://stackoverflow.com/questions/1562128/how-can-i-get-a-json-object-from-a-sql-server-table
-- eg.
-- exec dbo.GetJSON 'dbo', 'members'
-- exec dbo.GetJSON 'dbo', 'members', 2

create procedure [dbo].[GetJSON] (
	@schema_name varchar(50),
	@table_name varchar(50),
	@registries_per_request smallint = null
)
as
begin
	if ( ( select count(*) from information_schema.tables where table_schema = @schema_name and 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_schema = @schema_name
			and table_name = @table_name
		open schemaCursor
		
		fetch next from schemaCursor into @columnNavigator
		
		select @counter = count(*)
		from information_schema.columns
		where table_schema = @schema_name
		and 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 [' + @schema_name + '].[' + @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