SQL pagination returning total number of records before pagination

I use a stored proc to paginate server side on a big recordset The stored proc has a quite complex where clause.

I also开发者_JAVA百科 need the total amount of records before pagination to be able to visualize that number on the UI.

What is the best way of doing it?

at the moment I’m doing

ALTER PROCEDURE [Schema].[ReturnRecords]
@PageSize int = 20,
@StartIndex int = 0
AS
SELECT * FROM
(
    SELECT *, TOP(COUNT(e._ROWID) OVER ())
    FROM TABLE_NAME
    WHERE Column=1 AND Column1=2 AND Column2=2 AND Column3=2 AND Column4=2
) AS X
WHERE X.RowNum BETWEEN @StartIndex AND (@StartIndex + @PageSize)

so my last column of the record contains the total amount of records before pagination.

The alternative is to create a temporary table with the inner query, then count the records and return the temporary table but it does not look like a good way of doing it...

I would love to return the total number of records as an output parameter of the stored proc

any idea?

Doing a second query to get the count can often turn out to be a more efficient strategy. See some performance comparisons of various approaches here.