Get JSON from SQL Server

Procedure:

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

---------------------------------------------------------------------------------------------

When I execute it like this:

exec getJson 'announcement', 4

I'll get a single column named 'json' with *all* my data, in this case, 4 registries from a table named announcement. Something similar to this:

[{'Id':'1','Title':'<h3>1. Lorem ipsum dolor sit a','Date':'Jun 16 2008  5:44PM','Text':'<p>Lorem ipsum dolor sit amet,'},{'Id':'2','Title':'<h3>2. Quisque aliquet accumsa','Date':'Jun 16 2008  5:44PM','Text':'<p>Lorem ipsum dolor sit amet,'},{'Id':'3','Title':'<h3>3. Duis consequat laoreet<','Date':'Jun 16 2008  5:44PM','Text':'<ul><li>Duis consequat laoreet'},{'Id':'4','Title':'<h3>4. Aenean consectetuer</h3','Date':'Jun 16 2008  5:44PM','Text':'<p>Quisque aliquet accumsan le'}]

Then if you're using one JavaScript widget to slide news/announcements, it might be useful. Just pass this returning text to the eval method and you have a fresh json object, ready to use.

NOTE: it shouldn't be used with large amounts of data since you're populating a varchar sql variable, and it has a limit. If you try to pass more data than it can handle, you'll get errors.