StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>-- -- 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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload