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
.
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
)