A couple of weeks ago, I attended Houston’s first Open Innovation Hackathon and joined the team that had the goal of redeveloping the City of Houston City-Wide Fee Schedule .
The current fee schedule is an ASP.NET Web application with a SQL Server back end.
At the end of the hackathon, my team had put together a JavaScript-based site that used JSON to read in the data for the new site.
Since the current infrastructure was based on SQL Server, I sought a way to convert the two SQL tables into JSON Objects so the current back end would not have to be changed. As it turns out, I found a good starting point on Stack Overflow . The only problem with this stored procedure was that it would not work properly if there were single quotes in the results, or if the column names had spaces.
I was able to modify the stored proc to allow for these changes as well as a few others, such as accounting for NULL values. The modified SP is posted below for your reading pleasure!
(Link to gist)
/****** Object: StoredProcedure [dbo].[GetJSON] Script Date: 5/28/2013 4:31:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[GetJSON]
(
@table_name varchar(50),
@department varchar(50) = null,
@registries_per_request smallint = null
)
as
begin
--
-- 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
--
-- Modified by David Young (@deepinthecode http://twitter.com/deepinthecode
-- http://deepinthecode.com) to allow for a longer JSON return value,
-- changed table to temporary table --
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 int,
@size varchar(10),
@departmentname varchar(50)
set @departmentname = ''
if @department IS NOT NULL SET @departmentname = ',"ResponsibleDepartment":"'+convert(varchar,@department)+'"'
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 + '":"'' + isnull(convert(varchar, [' + @columnNavigator + ']),''N/A'') + ''"'
set @counter = @counter - 1
if(0 != @counter)
begin
set @columns = @columns + ','
end
fetch next from schemaCursor
into @columnNavigator
end
set @columns = @columns + @departmentname + '}'
close schemaCursor
deallocate schemaCursor
if OBJECT_ID('tempdb..#tmpJsonTable') IS NOT NULL
DROP TABLE #tmpJsonTable
CREATE TABLE #tmpJsonTable
(
json NVARCHAR(MAX) NULL
)
set @json = '['
set @sql = 'insert into #tmpJsonTable select ' + @size + '''' + @columns + ''' as json 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
end29.752554 -95.370401
Like this: Like Loading...
Related
I would take great measures to avoid doing this, ever.
If I were designing the whole system from the ground up, I wouldn’t do this either – but what is your reason?