CODEWITHSUNDEEP
sqlsql-serversql-server-2012
user1553136
user1553136

Reputation: 328

COUTING rows before OFFSET-FETCH

I have a stored procedure that looks like the following SQL code:

SELECT col1, col2, col3 FROM table1
LEFT JOIN table2
ON table1.col1 = table2.colWhatever
OFFSET @var1 ROWS FETCH NEXT @var2 ROWS ONLY

RETURN @@ROWCOUNT

However, I am not surprised that it returns a row count equal to @var2, but I wanted to know how many rows I had before applying the offset-fetch to the request.

What is the most clean and efficient way to do so ?

Upvotes: 1

Views: 259

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280439

You can add COUNT(*) OVER() to the SELECT list - you will get the total count as an additional column in the resultset (populated for each row), but you can pull that out when you first open the resultset and ignore it from then on.

Also, don't use RETURN to return the @@ROWCOUNT - it's for status and error codes, not for data. Use an OUTPUT parameter (but I assume you don't really want that value anyway, since it will return the same thing as @var2, which you already know).

Upvotes: 2

Related Questions