-- ==========================================================================
-- 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