Get Total Record Count when Paging in Stored Procedure using CTE in SQL Server

In this article I will explain with an example, how to get Total Record Count (Row Count) of Table when Paging in Stored Procedure 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 INT OUTPUT

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

 

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