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