Click here to Skip to main content
Click here to Skip to main content

Custom Paging Stored Procedure

, 30 Jan 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
It shows how to write custom paging procedure

Introduction

The main purpose of the article is to solve the data fetching problem with lakhs of records with custom paging using stored procedure.

Using the Code

Stored Procedure has parameters as listed below:

Parameter Name Type Remark
@StartRowIndex int Start row index
@MaxRows int Page size
@OrderByField nvarchar(200) Order by field name
@Asc bit If 1 then in Asc order else Desc
@SearchField nvarchar(200) Field for search
@SearchValue nvarchar(200) Search field value

This is the stored procedure given below:

-- =============================================
-- Author:	Mohan Prajapti	
-- Create date: 30 January 2009
-- Description:	Get Product Details
-- =============================================
ALTER PROCEDURE [dbo].[GetProducts]
@StartRowIndex int,
@MaxRows		int,
@OrderByField	nvarchar(200),
@Asc			bit,
@SearchField	nvarchar(200),
@SearchValue	nvarchar(200)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @ProductTable TABLE(ProductId uniqueidentifier,
				    ProductName nvarchar(200),
				    Description ntext,
				    Quantity int,
				    Price decimal(18,2))	

	DECLARE @WhereQuery nvarchar(max)

	SET @WhereQuery = ' WHERE '

	IF LEN(@SearchField) > 0
		BEGIN
			IF @SearchField = 'ProductName'
				SET	@WhereQuery = @WhereQuery + _
				' p_Name like ''%' + @SearchValue + '%'' '
			ELSE IF @SearchField = 'Quantity'
				SET	@WhereQuery = @WhereQuery +  _
				' p_Quantity =' + @SearchValue + ' '
			ELSE IF @SearchField = 'Price'	
				SET	@WhereQuery =  @WhereQuery + _
				' p_Price =' + @SearchValue + ' '
		END

	DECLARE @OrderQuery nvarchar(max)

	SET @OrderQuery = ' ORDER BY [p_Name] ' + _
		CASE @Asc WHEN 1 THEN ' ASC ' ELSE ' DESC ' END

	IF LEN(@OrderByField) > 0
		BEGIN
			IF @OrderByField = 'ProductName'
				SET	@OrderQuery = ' ORDER BY [p_Name] ' + _
					CASE @Asc WHEN 1 _
					THEN ' ASC ' ELSE ' DESC ' END
			ELSE IF @OrderByField = 'Quantity'
				SET	@OrderQuery = _
					' ORDER BY [p_Quantity] ' +  _
					CASE @Asc WHEN 1 _
					THEN ' ASC ' ELSE ' DESC '  END
			ELSE IF @OrderByField = 'Price'	
				SET	@OrderQuery = ' ORDER BY [p_Price] ' +  _
					CASE @Asc WHEN 1 _
					THEN ' ASC ' ELSE ' DESC '  END		
				
		END	
	
	DECLARE @Query  NVARCHAR(MAX)
	SET @Query = 'select	p_Id as ProductId, 
				p_Name as ProductName,
				p_Description as Description,
				p_Quantity as Quantity,
				p_Price as Price
			from 
			(

	
				SELECT 	ROW_NUMBER() OVER ('+ @OrderQuery +') _
					AS [ROW_NUMBER], 
						[t0].[p_Id], [t0].[p_Name], 
						[t0].[p_Description], 
						[t0].[p_Quantity], 
						[t0].[p_Price]
    					FROM [dbo].[Products] AS [t0]' + _
						@WhereQuery +
			') AS [t1]' +
			     + ' WHERE [ROW_NUMBER] BETWEEN _
			     	@StartRowIndex AND @MaxRows' + @OrderQuery

	PRINT @Query

	INSERT INTO @ProductTable EXEC sp_Executesql @Query,
					N'@OrderQuery nvarchar(max),_
				 	@StartRowIndex int,@MaxRows int',
					@OrderQuery=@OrderQuery,
					@StartRowIndex = @StartRowIndex,
					@MaxRows = @MaxRows
								
	SELECT * FROM @ProductTable			

    SET NOCOUNT OFF;
END
GO

Points of Interest

In this article, I have learnt an interesting function ROW_NUMBER() & sp_Executesql which is very useful nowadays. You can easily understand how to write a dynamic query.

History

  • 30th January, 2010: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mohan H Prajapati
Technical Lead
India India
I am software engineer with 3+ experience in asp.net and sql server 2005.

Comments and Discussions

 
Questionhelp me to change my store procedure for custom paging Pinmemberprince_rumeel20-Mar-13 2:21 
GeneralMy vote of 4 Pinmemberdigbyd22-Aug-11 2:13 
QuestionError en ejecución PinmemberHector19913-Jul-11 8:43 
GeneralNo need to use dynamic SQL PinmemberJohn Kasra16-Feb-10 11:18 
GeneralUseful PinmvpMd. Marufuzzaman2-Feb-10 6:27 
GeneralMy vote of 1 Pinmemberbabakzawari31-Jan-10 23:30 
GeneralSQL Server Version PinmemberDewey31-Jan-10 11:25 
GeneralRe: SQL Server Version PinmemberPranay Rana2-Feb-10 0:14 
GeneralRe: SQL Server Version PinmemberMember 2821278 (Mohan Prajapati)2-Feb-10 1:37 
GeneralSQL Injection PinmemberRonald Bosma31-Jan-10 0:57 
GeneralRe: SQL Injection Pinmemberspoodygoon31-Jan-10 4:54 
GeneralRe: SQL Injection PinmemberRonald Bosma31-Jan-10 5:41 
GeneralRe: SQL Injection Pinmemberspoodygoon31-Jan-10 7:40 
GeneralRe: SQL Injection PinmemberMember 2821278 (Mohan Prajapati)8-Feb-10 9:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.141022.2 | Last Updated 30 Jan 2010
Article Copyright 2010 by Mohan H Prajapati
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid