Custom Paging Stored 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

• 30^th January, 2010: Initial version