21 Nov 2008

Generating JSON from TSQL Query

No Comments SQL Server

I was looking for TSQL implementation which can build JSON payload directly from database. After couple of failed search, I decided to create my own stored procedure which can render JSON based for any query passed as parameter. After few keystrokes in SQL Server Management Studio, I have this semi-finish stored procedure which I thought you will find useful.

CREATE PROCEDURE [dbo].[GetJSON] (
@ParameterSQL AS VARCHAR(MAX)
)
AS
BEGIN
 
DECLARE @SQL NVARCHAR(MAX)
DECLARE @XMLString VARCHAR(MAX)
DECLARE @XML XML
DECLARE @Paramlist NVARCHAR(1000)
SET @Paramlist = N'@XML XML OUTPUT'
SET @SQL = 'WITH PrepareTable (XMLString) '
SET @SQL = @SQL + 'AS ( '
SET @SQL = @SQL + @ParameterSQL+ ' FOR XML RAW, TYPE, ELEMENTS '
SET @SQL = @SQL + ') '
SET @SQL = @SQL + 'SELECT @XML = XMLString FROM PrepareTable '
EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
SET @XMLString = CAST(@XML AS VARCHAR(MAX))
 
DECLARE @JSON VARCHAR(MAX)
DECLARE @Row VARCHAR(MAX)
DECLARE @RowStart INT
DECLARE @RowEnd INT
DECLARE @FieldStart INT
DECLARE @FieldEnd INT
DECLARE @KEY VARCHAR(MAX)
DECLARE @Value VARCHAR(MAX)
 
DECLARE @StartRoot VARCHAR(100); SET @StartRoot = '<row>'
DECLARE @EndRoot VARCHAR(100); SET @EndRoot = '</row>'
DECLARE @StartField VARCHAR(100); SET @StartField = '<'
DECLARE @EndField VARCHAR(100); SET @EndField = '>'
 
SET @RowStart = CharIndex(@StartRoot, @XMLString, 0)
SET @JSON = ''
WHILE @RowStart > 0
BEGIN
	SET @RowStart = @RowStart+Len(@StartRoot)
	SET @RowEnd = CharIndex(@EndRoot, @XMLString, @RowStart)
	SET @Row = SubString(@XMLString, @RowStart, @RowEnd-@RowStart)
	SET @JSON = @JSON+'{'
 
	-- for each row
	SET @FieldStart = CharIndex(@StartField, @Row, 0)
	WHILE @FieldStart > 0
	BEGIN
		-- parse node key
		SET @FieldStart = @FieldStart+Len(@StartField)
		SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)
		SET @KEY = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)
		SET @JSON = @JSON+'"'+@KEY+'":'
 
		-- parse node value
		SET @FieldStart = @FieldEnd+1
		SET @FieldEnd = CharIndex('</', @Row, @FieldStart)
		SET @Value = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)
		SET @JSON = @JSON+'"'+@Value+'",'
 
		SET @FieldStart = @FieldStart+Len(@StartField)
		SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)
		SET @FieldStart = CharIndex(@StartField, @Row, @FieldEnd)
	END	
	IF LEN(@JSON)>0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))
	SET @JSON = @JSON+'},'
	--/ for each row
 
	SET @RowStart = CharIndex(@StartRoot, @XMLString, @RowEnd)
END
IF LEN(@JSON)>0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))
SET @JSON = '[' + @JSON + ']'
SELECT @JSON
 
END

To use this stored procedure, just pass TSQL query as parameter. Something like this:

EXEC GetJSON 'SELECT * FROM dbo.Employee_TBL'

And this stored procedure will return JSON payload:

[{"UserId":"7C92EB27-DD81-498E-82CE-18192C940328","FirstName":"Bill","LastName":"Gates","Age":"45","LastLogin":"2008-11-02T00:00:00"},{"UserId":"58E75687-8D0F-423A-8A76-4B1D750F62FD","FirstName":"Steve","LastName":"Job","Age":"52","LastLogin":"2008-10-17T00:00:00"}]
15 Nov 2008

Getting list of modified objects in SQL Server

No Comments SQL Server, Uncategorized

Developers always want to have a list of modified tables or stored procedures while check-in the code or wrapping up the development at the end of the day. some usually keep this list in text file and they update this file every time they modify any table or stored procedure. Here are some handy SQL snippets which can provide you list of the tables, or stored procedure modified in specific duration.

-- tables modified today
SELECT [name],create_date,modify_date
FROM sys.objects
WHERE modify_date>DATEADD(day,-1,GETDATE())
AND type='U'
-- stored procedures modified today
SELECT [name],create_date,modify_date
FROM sys.objects
WHERE modify_date>DATEADD(day,-1,GETDATE())
AND type='P'

You can further modify above queries to get list with objects modified in last 7 days.

-- tables modified in last 7 days
SELECT [name],create_date,modify_date
FROM sys.objects
WHERE modify_date>DATEADD(day,-7,GETDATE())
AND type='U'
25 May 2006

Intellisense for SQL Server

No Comments SQL Server

So finally, SQL Query Analyzer has got feature which I was looking for long time. It’s SQL Prompt from Red Gate Software Ltd.

Intellisense for SQL Server

SQL Prompt provides intellisense support for Microsoft SQL Server editors. It will tell you the exact formatting you should use for your SQL commands, while you are writing them and will therefore help you write fast, perfectly formatted SQL statements. SQL Prompt improves the productivity of all SQL script creation.

You can download free version SQL Prompt from following location:
Downlod SQL Prompt

12 Jun 2005

SQL Server 2005 – Data Paging

No Comments SQL Server

It now become standard UI pattern to display large amount of records in paginated grid layout. For example, if there is huge list of employees, application will show only 10 employees at a time. If user wants to see next set of employees, he/she can click on next button of the grid, application will pull next set of 10 employees and render on page. By showing limited number of records, you can control the amount of payload move from server to browser, saving server engagement and consumed bandwidth.

There is one common technique used by most developers to induced qualified records into a temporary table and show paginated records from there. You can learn that approach from following links:

Manual Paging, part I
http://mceahern.manilasites.com/dotnet/pagingpart1

Paging: Use ADO, getrows, or a Stored Procedure?
http://www.15seconds.com/issue/010308.htm

Is Paging with Recordsets the Best Method?
http://www.15seconds.com/issue/010607.htm

The problem with creating temp table for paginated result create substantial overheads to database, which leads to performance issue down the line.

But now in SQL Server 2005, you don’t need to create those temp table. Microsoft extent SELECT statement with new ranking function ROW_NUMBER() which return row number in result set itself.

SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders

Which will provide result something like:

RowNumber            OrderID     OrderDate
-------------------- ----------- -----------------------
1                    10248       1996-07-04 00:00:00.000
2                    10249       1996-07-05 00:00:00.000
3                    10250       1996-07-08 00:00:00.000
4                    10251       1996-07-08 00:00:00.000
5                    10252       1996-07-09 00:00:00.000
6                    10253       1996-07-10 00:00:00.000
7                    10254       1996-07-11 00:00:00.000
8                    10255       1996-07-12 00:00:00.000
9                    10256       1996-07-15 00:00:00.000
10                   10257       1996-07-16 00:00:00.000

So now you can utilize row number to get qualify records from whole table. How? Here I go

WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders)
SELECT * 
FROM Ordered
WHERE RowNumber BETWEEN 21 AND 30

Above SQL snippet will display records from 21st to 30th. The result will look something like this:

RowNumber            OrderID     OrderDate
-------------------- ----------- -----------------------
21                   10268       1996-07-30 00:00:00.000
22                   10269       1996-07-31 00:00:00.000
23                   10270       1996-08-01 00:00:00.000
24                   10271       1996-08-01 00:00:00.000
25                   10272       1996-08-02 00:00:00.000
26                   10273       1996-08-05 00:00:00.000
27                   10274       1996-08-06 00:00:00.000
28                   10275       1996-08-07 00:00:00.000
29                   10276       1996-08-08 00:00:00.000
30                   10277       1996-08-09 00:00:00.000

I am sure this new SELECT extension will help a lot to work on paginated resultset.

12 Jun 2005

SQL Server 2005 (Beta 2) – Database Object Information HotKey

No Comments SQL Server

Inspecting object definition in Query Analyzer in SQL Server 2000 was very handy. If you want to review object definition without moving from your existing window, select that object in query pane and simply press ALT+F1. Voila!!!

But, it seems that this hot key is missing in SQL Server 2005. I hope this is just a bug in beta version and will be fixed in actual release.

About Me

My name is Firoz Ansari, and I'm software professional with passion for technology and design. I provide software solution and consulting services using Microsoft technologies in various business domains. more...

Recent Comments

  • Rac: hi, is there a way that i can implement it in c#? I tired copying the code inside my project but it doesnt ...
  • fasd: to mc: So true. ...
  • slash: I had to hex-quote the special characters to get the link to work on iPhone OS v3.0. Try pasting this into ...
  • William: I must agree. India is definitely moving ahead of all USA in terms of technology and outsourcing things. ...
  • Firoz Ansari: @ashishtandel No. You can’t remove “Web Page Dialog” from the title. You can ...