In this article I will explain with an example, how to write a Stored Procedure that does Paging along with Total Row Count in SQL Server.
This article will illustrate how to implement Paging in Stored Procedure using TEMP Tables 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 records with Row Number (Row Index) are inserted into a TEMP Table (Temporary Table) 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.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE PROCEDURE GetCustomersPageWise
@PageIndex INT
,@PageSize INT
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [CustomerID] ASC
)AS RowNumber
,[CustomerID]
,[CompanyName]
,[ContactName]
INTO #Results
FROM [Customers]
SELECT @RecordCount = COUNT(*)
FROM #Results
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
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 1, 10, @RecordCount OUTPUT
SELECT @RecordCount
Output
![Paging in SQL Server Stored Procedure with Total Row Count]()
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 1, 10, NULL
Output
![Paging in SQL Server Stored Procedure with Total Row Count]()
