In this article I will explain with an example, how to write a Stored Procedure that does Paging using Common Table Expression (CTE) 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
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE PROCEDURE GetCustomersPageWise_CTE
@PageIndex INT
,@PageSize INT
,@RecordCount INTOUTPUT
AS
BEGIN
SET NOCOUNT ON;
WITH PagingCTE AS
(
SELECT ROW_NUMBER() OVER
(
ORDER BY [CustomerID] ASC
) RowNumber
,[CustomerID]
,[CompanyName]
,[ContactName]
FROM [Customers]
)
SELECT * FROM
PagingCTE
WHERE RowNumber BETWEEN (@PageIndex -1) * @PageSize + 1 AND (((@PageIndex -1) * @PageSize + 1) + @PageSize)- 1
SELECT @RecordCount = COUNT([CustomerID])
FROM [Customers]
END
GO
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
SELECT @RecordCount
Output
![Paging in SQL Server Stored Procedure using Common Table Expression (CTE)]()
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
Output
![Paging in SQL Server Stored Procedure using Common Table Expression (CTE)]()
