I am attempting to do pagination inside a saved procedure in SQL Server, such as this:

/*Assign a row number to each row*/
SELECT
ROW_NUMBER() OVER (ORDER BY A, B, C ASC) AS ROW_NUMBER,
A, B, C
FROM ABC
WHERE ROW_NUMBER BETWEEN @startRecordNumber and @endRecordNumber

But, my calling code also really wants to know the number of outcome was within the original results set before pagination. So, I change my code for this:

/*Put the results into a temp table first*/
SELECT
ROW_NUMBER() OVER (ORDER BY A, B, C ASC) AS ROW_NUMBER,
A, B, C
INTO #TOTAL_RESULTS
FROM ABC

/*Get the total results from @@RowCount*/
declare @totalResults bigint
set @totalResults = @@RowCount

/*Now just get the desired page from the temp table*/
SELECT
A, B, C
FROM #TOTAL_RESULTS
WHERE ROW_NUMBER BETWEEN @startRecordNumber and @endRecordNumber

DROP TABLE #TOTAL_RESULTS

This feels pretty roundabout in my experience. Can there be any method of getting how big the initial result set without needing to create a temp table? Maybe only a Common Table Expression rather? I can not appear to discover a method to get it done.

Just in case it matters, here's ABC's schema:

ABC
A (PK, smallint not null)
B (PK, smallint not null)
C (PK, smallint not null)

You should use windowed aggregate functions. i.e COUNT(*) OVER() example below.

;WITH cte As
(
SELECT *, 
        ROW_NUMBER() OVER (ORDER BY number) AS RN,
        COUNT(*) OVER() AS Cnt
FROM master..spt_values
)
SELECT *
FROM cte 
WHERE RN BETWEEN 101 and 200

Or- Not just a serious suggestion but does avoid spools, work tables and double sorts :-)

DECLARE @Spid INT = @@Spid
DECLARE @TraceID INT

DECLARE @maxfilesize BIGINT = 5
DECLARE @filepath NVARCHAR(200) = N'C:\trace_' + LEFT(NEWID(),36)

EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL 

exec sp_trace_setevent @TraceID, 146, 1, 1
exec sp_trace_setevent @TraceID, 146, 22, 1
exec sp_trace_setevent @TraceID, 146, 34, 1
exec sp_trace_setevent @TraceID, 146, 51, 1
exec sp_trace_setevent @TraceID, 146, 12, 1
-- filter for spid
EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @Spid
-- start the trace
EXEC sp_trace_setstatus @TraceID, 1


;WITH cte AS
(
SELECT number, type, name, 
        ROW_NUMBER() OVER (ORDER BY number, type, name) AS RN
FROM master..spt_values
)
SELECT * FROM cte
WHERE RN BETWEEN 101 AND 200
OR RN+1 =0 /*To stop a "TOP 200" getting added to the plan*/

;WITH  XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)
     SELECT  ActualRows
         FROM   fn_trace_getinfo(@TraceID) fn
                CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1)
                CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x
                CROSS APPLY (SELECT T.relop.value('@ActualRows', 'INT') AS ActualRows
                             FROM   xPlan.nodes('//sql:RelOp[@LogicalOp="Segment"]/sql:RunTimeInformation/sql:RunTimeCountersPerThread') T(relop)) ca
         WHERE  property = 2
                AND ObjectName<>'fn_trace_getinfo' AND TextData NOT LIKE '%ThisQuery%'  

-- Stop the trace
EXEC sp_trace_setstatus @TraceID, 0
-- Close and delete the trace
EXEC sp_trace_setstatus @TraceID, 2

So far as I understand, it's not possible both to come back rows and also to assign variables simultaneously with SQL Server. If you want the row count to become saved right into a variable, you will need to have several statement one of the ways or another.

But when it's fine to come back the entire like a column and when you will find no replicates within the table through the A, B &lifier C posts, you can return the entire, for instance, such as this:

SELECT
  A, B, C,
  TotalResults = RowNumAsc + RowNumDesc - 1
FROM (
  SELECT
    A, B, C,
    RowNumAsc = ROW_NUMBER() OVER (ORDER BY A, B, C),
    RowNumDesc = ROW_NUMBER() OVER (ORDER BY A DESC, B DESC, C DESC)
  FROM
) s
WHERE RowNumAsc BETWEEN @startRecordNumber AND @endRecordNumber