Get JSON from SQL Server

On Thomas Frank's blog there is an interesting post which shows how to get JSON from MySql, and I though about doing the same to SQL Server since I use it more than any other database lately. So what I did was create a stored procedure which uses cursor to format the string with our data. I'm not really good with SQL, so I managed to use two, and the procedure itself isn't really what you would call a masterpiece. Sorry about that. But what I want to show here is just a concept tho.

Here's the Procedure.

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.

Published Monday, November 17, 2008 2:01 PM by tsantos
Filed under: ,

Comments

# Enlaces JSON y .NET

Monday, March 02, 2009 9:27 AM by Enlaces JSON y .NET

Pingback from  Enlaces JSON  y .NET

Leave a Comment

(required) 
(required) 
(optional)
(required)