In this article I will explain with an example, how to implement Paging in SQL Server Stored Procedure without using Temp Tables (Temporary Tables).
This article will illustrate how to write a Stored Procedure that does Paging using Common Table Expression (CTE) technique in SQL Server.
The Stored Procedure
The following Stored Procedure uses ROW_NUMBER function of SQL Server which was introduced in SQL Server 2005.
The ROW_NUMBER function assigns Row Number (Row Index) to each row of the Table in a specific Order.
Note: If you don’t have any specific column for ordering records then you can use the Primary Key for ordering the records.
The Row Number (Row Index) is then used to implement Paging using the PageIndex and PageSize parameters.
The Table fields along with the Row Number (Row Index) field are selected using Common Table Expression (CTE) and then the results are filtered based on the PageIndex and PageSize parameters.
RecordCount is an OUTPUT parameter which is used for returning the Total Record Count (Total Rows) of the Table.
Note: RecordCount is OPTIONAL and it can be removed if not needed.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE GetCustomersPageWise_CTE
,@RecordCount INT OUTPUT
SET NOCOUNT ON;
WITH PagingCTE AS
SELECT ROW_NUMBER() OVER
ORDER BY [CustomerID] ASC
SELECT * FROM
WHERE RowNumber BETWEEN (@PageIndex -1) * @PageSize + 1 AND (((@PageIndex -1) * @PageSize + 1) + @PageSize)- 1
SELECT @RecordCount = COUNT([CustomerID])
Executing the Stored Procedure
The Stored Procedure can be executed in two different ways.
1. With RecordCount
When the Count of the Total Rows in the Table is required then the RecordCount parameter is passed as OUTPUT parameter.
DECLARE @RecordCount INT
EXEC GetCustomersPageWise_CTE 1, 10, @RecordCount OUTPUT
2. Without RecordCount
When the Count of the Total Rows in the Table is not required then the RecordCount parameter is passed as NULL.
EXEC GetCustomersPageWise_CTE 1, 10, NULL