Sql server return number of rows from select stored procedure

Question

I use this query to get page and index limited result from table Customers. For example:

If @iStartIndex = 0 and @iRowsPerPage = 10 result is 10 rows from 0 - 10.

    SELECT 
        Name, LastName, City                       
        FROM Customers                                  
    OFFSET @iStartIndex ROWS
    FETCH NEXT @iRowsPerPage ROWS ONLY

My problem is that I also need a number of all rows from that query. For example: if there is 1000 rows, query will select only 10 rows. I need those 10 rows, but I also need to know how many rows is in the result - in this case 1000 rows.

What I've already tried is to return @@ROWCOUNT, but it returns 10, the result of the last query. I've also tried to write one separate query:

SELECT COUNT(*)                
FROM Customers

and below I wrote original query.

Is there a way to return rows, but also to return number of rows as scalar value in the same stored procedure?

You'll have to do two queries, no way around it. You can still combine those queries into one result set if you like (by assigning the result of the first to a variable) or use an output parameter. (Technically, you could do it in one query by stuffing the first one in a subquery, but that's really still two queries in the background.) — Jeroen Mostert
– Jeroen Mostert, Commented Nov 8, 2016 at 14:15
Umm...if there are 10 rows in the result can you explain how 10 is not the right number? If you want to get the total number of customers you would have to do that as a separate query (or a subquery). — Sean Lange
– Sean Lange, Commented Nov 8, 2016 at 14:16
It can be done with a windowed aggregate but most people report the performance as worse than running two separate queries — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Nov 8, 2016 at 14:19
@SeanLange - I also need number of all records. I use jTable for displaying records and paging needs number of all records. — FrenkyB
– FrenkyB, Commented Nov 8, 2016 at 14:31

Ross Bush · Accepted Answer · 2016-11-08 14:16:41Z

1

I think you are looking for something like this. Just return the total with the result set. It is not uncommon, especially in SP's for ssrs and other reporting tools to return duplicate data for each record.

DECLARE @TotalCount INT=SELECT COUNT(*) FROM Customers  
SELECT 
    Name, LastName, City, TotalCount=@TotalCount                       
    FROM Customers                                  
OFFSET @iStartIndex ROWS
FETCH NEXT @iRowsPerPage ROWS ONLY

answered Nov 8, 2016 at 14:16

Ross Bush

15.2k2 gold badges39 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FrenkyB Over a year ago

I've thought of that solution. too. After all result will be repeated in 10 rows. If this not so uncommon, why not.

Ross Bush Over a year ago

The overhead of calling two commands and the possibility of creating two database connections to get a total count far outweigh the additional payload in this case.I would say, even for a few hundred records, it is more efficient to duplicate a few fields of metadata or aggregations.

Collectives™ on Stack Overflow

Sql server return number of rows from select stored procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related