JSON formatted output in SQL Server

Problem: Customer wishes to see output from a stored proc in JSON format

Solution: 

As always first stop is Google and I found a nice JSON transformer script which will do the job

http://www.adampresley.com/2010/07/experimenting-with-sql-to-json-in-sql.html

The code we are interested in can be found here jsuntest.cs

Compile the code by opening up a command prompt at C:\Windows\Microsoft.NET\Framework\v3.5 and run csc.exe /target:library /out:jsun.dll jsuntest.cs

This will create a dll named jsun from jsun.cs file which we have placed in C:\Windows\Microsoft.NET\Framework\v3.5

Next we create the assembly in SQL Server

CREATE ASSEMBLY jsun FROM ‘C:\Windows\Microsoft.NET\Framework\v3.5\jsun.dll

Now Create a function in your database

CREATE FUNCTION [dbo].[SQLJsonEncode](@a [nvarchar](4000), @b [nvarchar](4000), @c [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [jsun].[JSONTransformer].[Encode]

finally create the stored proc which creates your dataset and outputs it in JSUN format

CREATE Proc jsun_demo

@customer_number INT

AS

DECLARE @resultSetXML VARCHAR(max);
DECLARE @rootNodeName VARCHAR(75);
DECLARE @elementNodeName VARCHAR(75);
DECLARE @result VARCHAR(max);

SET @rootNodeName = ‘customer’;
SET @elementNodeName = ‘customer’;

DECLARE @SQL VARCHAR(MAX)=

‘SELECT CustomerID,field1,field2 FROM SOMETABLES

WHERE CustomerID=’ + cast(@customer_number as varchar(10)) + ‘)’
DECLARE @result_set TABLE
(
CustomerNumber INT
,field1 VARCHAR(MAX)
,field2 VARCHAR(MAX)
)
insert @result_set
exec (@SQL)

SET @resultSetXML = (
select *
from @result_set customer FOR XML AUTO, ROOT(‘customer’)
)

SELECT dbo.SQLJsonEncode(@resultSetXML, @rootNodeName, @elementNodeName) AS jsonResult;

GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: