Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

Custom Paging Stored Procedure

3.75/5 (7 votes)
30 Jan 2010CPOL 49.3K  
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 NameTypeRemark
@StartRowIndexintStart row index
@MaxRowsintPage size
@OrderByFieldnvarchar(200)Order by field name
@AscbitIf 1 then in Asc order else Desc
@SearchFieldnvarchar(200)Field for search
@SearchValuenvarchar(200)Search field value

This is the stored procedure given below:

SQL
-- =============================================
-- 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)