sqlsql-server-2005paginationrowcount

Send the total rows/last row, included with the resultset


I have this procedure used for getting items on the current page. I would also like the include an OUT var having the total number of items so I can calculate the total number of pages.

USE [some_name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetFilteredProductDetails]
    @start int,
    @end int
AS
BEGIN
    SET NOCOUNT ON;

        SELECT *
        FROM
        (
            SELECT *, (ROW_NUMBER() OVER (ORDER BY itemid)) AS row
            /* the rest of a big complex query that, so far, works.*/
        ) AS q
        WHERE
        (           
            row BETWEEN @start AND @end
        )
END

This is my current (stripped)query, how would I be able to get either get the last rownumber/total rowcount of the inner select, or include the last row alongside of the rows between @start and @end.


Solution

  • COUNT(*) with an empty OVER() clause will give you the total row count. You could then add that into the WHERE clause if you need the last row returned.

    SELECT *
    FROM
    (
        SELECT *, 
        (ROW_NUMBER() OVER (ORDER BY itemid)) AS row,
        COUNT(*) OVER() AS row_count
        /* the rest of a big complex query that, so far, works.*/
    ) AS q
     WHERE
        (           
            row BETWEEN @start AND @end or row=row_count
        )