Advertisements

Archive

Archive for December, 2011

Creating Stored Procedure with Dynamic Search, Paging and Sorting

December 30, 2011 12 comments

In my last two previous post we learned to apply [Dynamic Search Capability] and [Pagination] in an SP.

Here in this post we will see how can we apply Sorting at different columns of the returned record set from a Stored Procedure.

In webpage’s data grids when you click on the column header the records are sorted according to the selected column. When you again click that column the ordering reverses for that column. So to implement this functionality we will be passing an extra parameter to the SP which will contain the Column name and sort order, DESC or ASC.

Let’s check this with a sample code using [Person].[Contact] Table of [AdventureWorks] Database:

USE [AdventureWorks]
GO

CREATE PROCEDURE USP_GET_Contacts_DynSearch_Paging_Sorting
(
	-- Optional Filters for Dynamic Search
	@ContactID			INT = NULL, 
	@FirstName			NVARCHAR(50) = NULL, 
	@LastName			NVARCHAR(50) = NULL, 
	@EmailAddress		NVARCHAR(50) = NULL, 
	@EmailPromotion		INT = NULL, 
	@Phone				NVARCHAR(25) = NULL,
	-- Pagination
	@PageNbr			INT = 1,
	@PageSize			INT = 10,
	-- Sort Details
	@SortCol			NVARCHAR(20) = ''
)
AS
BEGIN
	DECLARE 
		@lContactID			INT, 
		@lFirstName			NVARCHAR(50), 
		@lLastName			NVARCHAR(50), 
		@lEmailAddress		NVARCHAR(50), 
		@lEmailPromotion	INT, 
		@lPhone				NVARCHAR(25)
	
	DECLARE 
		@lPageNbr	INT,
		@lPageSize	INT,
		@lSortCol	NVARCHAR(20),
		@lFirstRec	INT,
		@lLastRec	INT,
		@lTotalRows INT

	SET @lContactID			= LTRIM(RTRIM(@ContactID))
	SET @lFirstName			= LTRIM(RTRIM(@FirstName))
	SET @lLastName			= LTRIM(RTRIM(@LastName))
	SET @lEmailAddress		= LTRIM(RTRIM(@EmailAddress))
	SET @lEmailPromotion	= @EmailPromotion
	SET @lPhone				= LTRIM(RTRIM(@Phone))

	SET @lPageNbr	= @PageNbr
	SET @lPageSize	= @PageSize
	SET @lSortCol	= LTRIM(RTRIM(@SortCol))
	
    SET @lFirstRec	= ( @lPageNbr - 1 ) * @lPageSize
    SET @lLastRec	= ( @lPageNbr * @lPageSize + 1 ) 
    SET @lTotalRows = @lFirstRec - @lLastRec + 1

    ; WITH CTE_Results
    AS (
		SELECT ROW_NUMBER() OVER (ORDER BY 
			CASE WHEN @lSortCol = 'ContactID_Asc' THEN ContactID
				END ASC,
			CASE WHEN @lSortCol = 'ContactID_Desc' THEN ContactID
				END DESC, 

			CASE WHEN @lSortCol = 'Title_Asc' THEN Title
				END ASC,
			CASE WHEN @lSortCol = 'Title_Desc' THEN Title
				END DESC, 

			CASE WHEN @lSortCol = 'FirstName_Asc' THEN FirstName
				END ASC,
			CASE WHEN @lSortCol = 'FirstName_Desc' THEN FirstName
				END DESC, 

			CASE WHEN @lSortCol = 'MiddleName_Asc' THEN MiddleName
				END ASC,
			CASE WHEN @lSortCol = 'MiddleName_Desc' THEN MiddleName
				END DESC, 

			CASE WHEN @lSortCol = 'LastName_Asc' THEN LastName
				END ASC,
			CASE WHEN @lSortCol = 'LastName_Desc' THEN LastName
				END DESC, 

			CASE WHEN @lSortCol = 'Suffix_Asc' THEN Suffix
				END ASC,
			CASE WHEN @lSortCol = 'Suffix_Desc' THEN Suffix
				END DESC, 

			CASE WHEN @lSortCol = 'EmailAddress_Asc' THEN EmailAddress
				END ASC,
			CASE WHEN @lSortCol = 'EmailAddress_Desc' THEN EmailAddress
				END DESC, 

			CASE WHEN @lSortCol = 'EmailPromotion_Asc' THEN EmailPromotion
				END ASC,
			CASE WHEN @lSortCol = 'EmailPromotion_Desc' THEN EmailPromotion
				END DESC, 

			CASE WHEN @lSortCol = 'Phone_Asc' THEN Phone
				END ASC,
			CASE WHEN @lSortCol = 'Phone_Desc' THEN Phone
				END DESC

			) AS ROWNUM,
			ContactID, 
			Title, 
			FirstName, 
			MiddleName, 
			LastName, 
			Suffix, 
			EmailAddress, 
			EmailPromotion, 
			Phone
		FROM Person.Contact
		WHERE 
			(@lContactID IS NULL OR ContactID = @lContactID)
		AND (@lFirstName IS NULL OR FirstName LIKE '%' + @lFirstName + '%')
		AND (@lLastName IS NULL OR LastName LIKE '%' + @lLastName + '%')
		AND (@lEmailAddress IS NULL OR EmailAddress LIKE '%' + @lEmailAddress + '%')
		AND (@lEmailPromotion IS NULL OR EmailPromotion = @lEmailPromotion)
		AND (@lPhone IS NULL OR Phone = @lPhone)
	)
	SELECT 
		ContactID, 
		Title, 
		FirstName, 
		MiddleName, 
		LastName, 
		Suffix, 
		EmailAddress, 
		EmailPromotion, 
		Phone
	FROM CTE_Results AS CPC
    WHERE 
		ROWNUM > @lFirstRec 
	AND ROWNUM < @lLastRec
	ORDER BY ROWNUM ASC 
		
END
GO

OK, now let’s test this SP:

-- No parameters provided, fetch first 10 default records:
EXEC USP_GET_Contacts_DynSearch_Paging_Sorting

-- On providing @SortCol = 'FirstName_Desc', will fetch 20 records sorted by FirstName:
EXEC USP_GET_Contacts_DynSearch_Paging_Sorting @SortCol = 'FirstName_Asc'

-- On providing @SortCol = 'LastName_Asc', will fetch 20 records sorted by LastName in descending order:
EXEC USP_GET_Contacts_DynSearch_Paging_Sorting @SortCol = 'LastName_Desc'

-- Following query will pull out 20 records in a page having FirstName LIKE 'Sam' sorted by LastName in Descending order:
EXEC USP_GET_Contacts_DynSearch_Paging_Sorting @FirstName = 'Sam', @PageSize = 20, @SortCol = 'LastName_Desc' 

Appreciate your valuable comments if we can do this in a different or/and better way.

Advertisements

Creating Stored Procedures with Dynamic Search & Paging (Pagination)

December 30, 2011 4 comments

In my previous post we learned how we can apply Dynamic Search capabilities to an SP.

Here we will see how can we apply paging (or pagination) on the returned recordsets from a Stored Procedures.

Pagination is required when lot of records are returned from an SP and the webpage becomes very heavy to load it, like more than 1k records. And it also becomes difficult for a user to handle that much records and view in a single screen.

Many webpages displaying records has First, Previous, Next & Last buttons on top and bottom of the data grid. So to make the buttons function we’ve to implement the paging functionality on SPs itself.

Let’s check with a sample code using [Person].[Contact] Table of [AdventureWorks] Database:

CREATE PROCEDURE USP_GET_Contacts_DynSearch_Paging
(
	-- Pagination
	@PageNbr			INT = 1,
	@PageSize			INT = 10,
	-- Optional Filters for Dynamic Search
	@ContactID			INT = NULL, 
	@FirstName			NVARCHAR(50) = NULL, 
	@LastName			NVARCHAR(50) = NULL, 
	@EmailAddress		NVARCHAR(50) = NULL, 
	@EmailPromotion		INT = NULL, 
	@Phone				NVARCHAR(25) = NULL
)
AS
BEGIN
	DECLARE 
		@lContactID			INT, 
		@lFirstName			NVARCHAR(50), 
		@lLastName			NVARCHAR(50), 
		@lEmailAddress		NVARCHAR(50), 
		@lEmailPromotion	INT, 
		@lPhone				NVARCHAR(25)
	
	DECLARE 
		@lPageNbr	INT,
		@lPageSize	INT,
		@lFirstRec	INT,
		@lLastRec	INT,
		@lTotalRows INT

	SET @lContactID			= @ContactID
	SET @lFirstName			= LTRIM(RTRIM(@FirstName))
	SET @lLastName			= LTRIM(RTRIM(@LastName))
	SET @lEmailAddress		= LTRIM(RTRIM(@EmailAddress))
	SET @lEmailPromotion	= @EmailPromotion
	SET @lPhone				= LTRIM(RTRIM(@Phone))

	SET @lPageNbr	= @PageNbr
	SET @lPageSize	= @PageSize
	
    SET @lFirstRec	= ( @lPageNbr - 1 ) * @lPageSize
    SET @lLastRec	= ( @lPageNbr * @lPageSize + 1 ) 
    SET @lTotalRows = @lFirstRec - @lLastRec + 1

    ; WITH CTE_Results
    AS (
		SELECT ROW_NUMBER() OVER (ORDER BY ContactID) AS ROWNUM,
			ContactID, 
			Title, 
			FirstName, 
			MiddleName, 
			LastName, 
			Suffix, 
			EmailAddress, 
			EmailPromotion, 
			Phone
		FROM Person.Contact
		WHERE 
			(@lContactID IS NULL OR ContactID = @lContactID)
		AND (@lFirstName IS NULL OR FirstName LIKE '%' + @lFirstName + '%')
		AND (@lLastName IS NULL OR LastName LIKE '%' + @lLastName + '%')
		AND (@lEmailAddress IS NULL OR EmailAddress LIKE '%' + @lEmailAddress + '%')
		AND (@lEmailPromotion IS NULL OR EmailPromotion = @lEmailPromotion)
		AND (@lPhone IS NULL OR Phone = @lPhone)
	)
	SELECT 
		ContactID, 
		Title, 
		FirstName, 
		MiddleName, 
		LastName, 
		Suffix, 
		EmailAddress, 
		EmailPromotion, 
		Phone
	FROM CTE_Results AS CPC
    WHERE 
		ROWNUM > @lFirstRec 
	AND ROWNUM < @lLastRec
	ORDER BY ROWNUM ASC 

END
GO

OK, now let’s test this SP:

-- No parameters provided, fetch first 10 default records:
EXEC USP_GET_Contacts_DynSearch_Paging

-- On providing @PageSize=20, will fetch 20 records:
EXEC USP_GET_Contacts_DynSearch_Paging @PageSize=20

-- On providing @PageNbr=2, @PageSize=10, will display second page, ContactID starting from 11 to 20:
EXEC USP_GET_Contacts_DynSearch_Paging @PageNbr=2, @PageSize=10

-- On providing @PageNbr=1, @PageSize=50, @FirstName = 'Sam', it will search FurstName like Sam and will fetch first 50 records:
EXEC USP_GET_Contacts_DynSearch_Paging @PageNbr=1, @PageSize=50, @FirstName = 'Sam'

SQL Server 2012 (a.k.a Denali) has a new mechanism to implement pagination by using OFFSET FETCH clause, check it here in my blog post.

Appreciate your valuable comments if we can do this in a different or/and better way.

Creating Stored Procedures with Dynamic Search (filter)

December 30, 2011 14 comments

Stored Procedures are a set of single or group of Transact-SQL statements with or without control flow and other business logic to query database tables and get us our desired results. They also assist us in achieving a consistent implementation of logic across applications and also improve performance. I will discuss the benefits on these in another separate post.

Here we will see how can we create Stored Procedures with Dynamic search capabilities.
 

By Dynamic search capability I mean whatever combination of input provided to the Stored Procedure as parameters it would filter and fetch the expected records for us.
 

–> Let’s check with a sample code using [AdventureWorks] Database and [Person].[Contact] Table:

USE [AdventureWorks]
GO

CREATE PROCEDURE USP_GET_Contacts_DynSearch
(
	-- Optional Filters for Dynamic Search
	@ContactID			INT = NULL, 
	@FirstName			NVARCHAR(50) = NULL, 
	@LastName			NVARCHAR(50) = NULL, 
	@EmailAddress		NVARCHAR(50) = NULL, 
	@EmailPromotion		INT = NULL, 
	@Phone				NVARCHAR(25) = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE 
		@lContactID			INT, 
		@lFirstName			NVARCHAR(50), 
		@lLastName			NVARCHAR(50), 
		@lEmailAddress		NVARCHAR(50), 
		@lEmailPromotion	INT, 
		@lPhone				NVARCHAR(25)
	
	SET @lContactID			= @ContactID
	SET @lFirstName			= LTRIM(RTRIM(@FirstName))
	SET @lLastName			= LTRIM(RTRIM(@LastName))
	SET @lEmailAddress		= LTRIM(RTRIM(@EmailAddress))
	SET @lEmailPromotion	= @EmailPromotion
	SET @lPhone				= LTRIM(RTRIM(@Phone))

	SELECT 
		ContactID, 
		Title, 
		FirstName, 
		MiddleName, 
		LastName, 
		Suffix, 
		EmailAddress, 
		EmailPromotion, 
		Phone
	FROM [Person].[Contact]
	WHERE 
		(@lContactID IS NULL OR ContactID = @lContactID)
	AND (@lFirstName IS NULL OR FirstName LIKE '%' + @lFirstName + '%')
	AND (@lLastName IS NULL OR LastName LIKE '%' + @lLastName + '%')
	AND (@lEmailAddress IS NULL OR EmailAddress LIKE '%' + @lEmailAddress + '%')
	AND (@lEmailPromotion IS NULL OR EmailPromotion = @lEmailPromotion)
	AND (@lPhone IS NULL OR Phone = @lPhone)
	ORDER BY ContactID
		
END
GO

 

–> OK, now let’s test this SP:

-- No parameters provided, it will fetch all records:
EXEC USP_GET_Contacts_DynSearch -- 19972 records

-- On providing only FirstName param:
EXEC USP_GET_Contacts_DynSearch @FirstName = 'john' -- 104 records

-- On providing a combination of FirstName & LastName params:
EXEC USP_GET_Contacts_DynSearch @FirstName = 'john', @LastName = 'c' -- 19 records

-- On providing a combination of FirstName, LastName & EmailAddress params:
EXEC USP_GET_Contacts_DynSearch @FirstName = 'john', @LastName = 'c', @EmailAddress = '3' -- 6 records

-- On providing only EmailAddress param:
EXEC USP_GET_Contacts_DynSearch @EmailAddress = 'samuel' -- 73 records

In the above exercise we can see that we can filter or records based upon different or a combination of different parameters and get our desired results.
 

Please Note: In WHERE clause condition you can use equal-to operator or the LIKE, or BETWEEN depending upon your requirements.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 


Categories: Stored Procedures Tags:

Recursive CTE error – The maximum recursion 100 has been exhausted before statement completion

December 23, 2011 10 comments

One of the most benefit of CTE (Common Table Expressions) is that we can create recursive queries with them. In my previous posts I’ve discussed this topic with some good examples, link.

An incorrectly composed recursive CTE may cause an infinite loop. So recursive CTEs should be designed very carefully and the recursion level should be checked. To prevent it to run infinitely SQL Server’s default recursion level is set to 100. But you can change the level by using the MAXRECURSION option/hint. The recursion level ranges from 0 and 32,767.

If your CTEs recursion level crosses the limit then following error is thrown by SQL Server engine:
Msg 530, Level 16, State 1, Line 11
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Let’s check this with an example discussed in MSDN TSQL forum, link:

DECLARE
	@startDate DATETIME,
	@endDate DATETIME

SET @startDate = '11/10/2011'
SET @endDate = '03/25/2012'

; WITH CTE AS (
	SELECT
		YEAR(@startDate) AS 'yr',
		MONTH(@startDate) AS 'mm',
		DATENAME(mm, @startDate) AS 'mon',
		DATEPART(d,@startDate) AS 'dd',
		@startDate 'new_date'
	UNION ALL
	SELECT
		YEAR(new_date) AS 'yr',
		MONTH(new_date) AS 'mm',
		DATENAME(mm, new_date) AS 'mon',
		DATEPART(d,@startDate) AS 'dd',
		DATEADD(d,1,new_date) 'new_date'
	FROM CTE
	WHERE new_date < @endDate
	)
SELECT yr AS 'Year', mon AS 'Month', count(dd) AS 'Days'
FROM CTE
GROUP BY mon, yr, mm
ORDER BY yr, mm
OPTION (MAXRECURSION 1000)
Output:-

Year	Month		Days
2011	November	22
2011	December	31
2012	January		31
2012	February	29
2012	March		24

Here, by applying “OPTION (MAXRECURSION 1000)”, we can set the recursion level, so that it does not go infinite.

Note: Restriction of recursive CTE is – “A view that contains a recursive CTE cannot be used to update data”.

More info on: http://msdn.microsoft.com/en-us/library/ms175972.aspx

MSDN & Online Technical Articles on using XML with SQL

December 22, 2011 Leave a comment

On this post I’m not going to discuss anything about XML. Here I’m listing out some links that I found very informative so that I can refer to them easily in future, and so can you.

My all previous posts on XML-SQL can be found at: https://sqlwithmanoj.wordpress.com/category/xml/

 

There are also lot of Technical Articles in MS BOL that discuss about using XML in SQL Server.

Here is a list of those:

What’s New in FOR XML: http://msdn.microsoft.com/en-US/library/ms345137(v=SQL.90).aspx

XML Best Practices: http://msdn.microsoft.com/en-US/library/ms345115(v=SQL.90).aspx

XML Indexes: http://msdn.microsoft.com/en-US/library/ms345121(v=SQL.90).aspx

XML Options: http://msdn.microsoft.com/en-US/library/ms345110(v=SQL.90).aspx

XML Support: http://msdn.microsoft.com/en-US/library/ms345117(v=SQL.90).aspx

Performance Optimizations for the XML Data Type: http://msdn.microsoft.com/en-US/library/ms345118(v=SQL.90).aspx

… enjoy reading.

 

Please post your comments and links if you found any informative online article about XML-SQL.