Total RecordCount as OUTPUT of Paged Result Set in Stored Procedure

Question

i have a question on stored procedures.

I try to get a page of result set and the record count of the whole set.

Each of this is working on it's own, but I'm unable to combine it:

ALTER PROCEDURE dbo.pagingSCP
@PageStart INT, 
@PageSize INT, 
@RecordCount INT OUTPUT
AS
BEGIN
WITH AllRecords AS ( 
SELECT ROW_NUMBER() OVER (ORDER BY MATNR) 
AS Row, viewStyleColorInModul.*
FROM viewStyleColorInModul WHERE SPRAS = 'D'
) SELECT * FROM AllRecords WHERE Row between 
@PageStart and @PageStart + @PageSize
END

(50 row(s) returned) @RecordCount = 0 @RETURN_VALUE = 0 Finished running [dbo].[pagingSCP].

ALTER PROCEDURE dbo.pagingSCP
@PageStart INT, 
@PageSize INT, 
@RecordCount INT OUTPUT
AS
BEGIN
WITH AllRecords AS ( 
SELECT ROW_NUMBER() OVER (ORDER BY MATNR) 
AS Row, viewStyleColorInModul.*
FROM viewStyleColorInModul WHERE SPRAS = 'D'
) SELECT @RecordCount = Count(*) From AllRecords
END

No rows affected. (0 row(s) returned) @RecordCount = 43770 @RETURN_VALUE = 0 Finished running [dbo].[pagingSCP].

Is is now somehow possible to get the 50 Rows and the total Recordcount within the single query?

Thanks in advance.

Answer 1

ALTER PROCEDURE dbo.pagingSCP
@PageStart INT, 
@PageSize INT, 
@RecordCount INT OUTPUT
AS
BEGIN
  -- get record count
  WITH AllRecords AS ( 
    SELECT viewStyleColorInModul.*
  FROM viewStyleColorInModul WHERE SPRAS = 'D'
  ) SELECT @RecordCount = Count(*) From AllRecords;

  -- now get the records
  WITH AllRecords AS ( 
   SELECT ROW_NUMBER() OVER (ORDER BY MATNR) 
   AS Row, viewStyleColorInModul.*
   FROM viewStyleColorInModul WHERE SPRAS = 'D'
  ) SELECT * FROM AllRecords 
  WHERE Row between @PageStart and @PageStart + @PageSize;
END

You have two distinct queries, therefor eyou run two distinct SELECT and let the SQL optimizer optimize each individually. Even if trying to get both queries in one SELECT is possible, is highly counterproductive and sub-optimal.

As a side note, in the client code any output parameter is available only after iterating all results returned.