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.ProductID         INNER JOIN Nop_ProductVariant pv ON p.ProductID = pv.ProductID         INNER JOIN Nop_ProductVariant_Warehouse_Mapping wpv ON pv.ProductVariantID = wpv.ProductVariantID         WHERE pcm.CategoryID = @CategoryId AND (wpv.Published = 1 AND pv.Published = 1 AND p.Published = 1 AND p.Deleted = 0)         AND wpv.WarehouseID IN (select WarehouseID from Nop_WarehouseCombination where UserWarehouseCombinationID = @WarehouseCombinationID)         )     SELECT TOP (@PageSize) * FROM Paging PG     WHERE PG.rn > (@PageNumber * @PageSize) - @PageSize       SET @TotalRecords = @@ROWCOUNT       END 

Answer 1

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.