Return total records from SQL Server when using ROW_NUMBER

Question

I would like to return the total number of records in the database so I can set up pagination. How do I return the total number of records in the DB when using the following paging method in SQL Server 2008? ``` ALTER PROCEDURE [dbo].[Nop_LoadAllOptimized] ( @PageSize int = 20, @PageNumber int = 1, @WarehouseCombinationID int = 1, @CategoryId int = 58, @OrderBy int = 0, @TotalRecords int = null OUTPUT ) AS BEGIN WITH Paging AS ( SELECT rn = (ROW_NUMBER() OVER ( ORDER BY CASE WHEN @OrderBy = 0 AND @CategoryID IS NOT NULL AND @CategoryID > 0 THEN pcm.DisplayOrder END ASC, CASE WHEN @OrderBy = 0 THEN p.[Name] END ASC, CASE WHEN @OrderBy = 5 THEN p.[Name] END ASC, CASE WHEN @OrderBy = 10 THEN wpv.Price END ASC, CASE WHEN @OrderBy = 15 THEN wpv.Price END DESC, CASE WHEN @OrderBy = 20 THEN wpv.Price END DESC, CASE WHEN @OrderBy = 25 THEN wpv.UnitPrice END ASC )), p.*, pcm.DisplayOrder, wpv.Price, wpv.UnitPrice FROM Nop_Product p INNER JOIN Nop_Product_Category_Mapping pcm ON p.ProductID=pcm…

Accepted Answer

I typically do it this way - never really checked whether it's very efficient from a performance point of view:

```
WITH YourCTE AS 
(
   SELECT 
       (list of columns),
       ROW_NUMBER() OVER (ORDER BY ......) AS 'RowNum' 
   FROM dbo.YourBaseTable
)
SELECT 
    *,
    (SELECT MAX(RowNum) FROM YourCTE) AS 'TotalRows' 
FROM 
    YourCTE
WHERE   
    RowNum BETWEEN 101 AND 150
```

Basically, the `RowNum` value will have values 1 through the total of rows (if you don't have a `PARTITION BY` in your CTE) and thus selecting `MAX(RowNum)`, you get the total number of rows.

Return total records from SQL Server when using ROW_NUMBER

Solution

Problem

Related problems