title: Sql server return number of rows from select stored procedure
body: <p>I use this query to get page and index limited result from table Customers. For example:</p>
  
  <p>If @iStartIndex = 0 and @iRowsPerPage = 10 result is 10 rows from 0 - 10.</p>
  
  <pre><code>    SELECT 
          Name, LastName, City                       
          FROM Customers                                  
      OFFSET @iStartIndex ROWS
      FETCH NEXT @iRowsPerPage ROWS ONLY
  </code></pre>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>SELECT COUNT(*)                
  FROM Customers  
  </code></pre>
  
  <p>and below I wrote original query. </p>
  
  <p>Is there a way to return rows, but also to return number of rows as scalar value in the same stored procedure? </p>