≡ Menu
Deep in the Code

Converting a SQL Server Table into a JavaScript Object Using a Stored Procedure

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)