How to return rows AND a variable from a SQL Server Stored Procedure

Hopefully this is simple. I'm optimizing a stored procedure that returns about 500+ rows. My plan is to return the rows in batches until there are no more rows to get.

For example, I'll get rows 0-49 -- then 50-99, then 100-149, and so on..

I've accomplished this using the following SQL code:

CREATE PROCEDURE [dbo].[mySP]  
@rowstart int,
@rowend int   

AS  

WITH MainQuery AS

(
  HUGE SELECT STATEMENT HERE
)

select * from MainQuery where row between @rowstart and @rowend

When I execute this SP, I simply pass in values for rowstart and rowend and it will return the range of rows I want perfectly.

Problem is, I want to know that there are MORE rows to get after each query. I think I can accomplish that by returning @@ROWCOUNT after the MainQuery block completes, but I don't know how to get the range of ro开发者_运维技巧ws returned AND a value for @@ROWCOUNT after each time the SP is executed.

When I make the initial query of getting 50 rows returned, if I could know that there are 503 TOTAL table rows, I can do some simple math (503/50) and figure out how many more times I need to call the SP. Any help is appreciated!

Have you tried output parameters?

http://msdn.microsoft.com/en-us/library/ms378108%28v=sql.90%29.aspx

The other answers will tell you how to get the answer out of the stored proc...

I'd like to point out that @@ROWCOUNT will be @rowend - @rowstart + 1. So, except for the last page you have 50 rows. For the last page you can get the row count in the client from, say, the DataTable.

What you need is something like this if you want it from SQL

WITH MainQuery AS
    (
  HUGE SELECT STATEMENT HERE
)
select * from MainQuery
CROSS JOIN
(SELECT COUNT(*) AS TotalRows FROM MainQuery) MC
 where row between @rowstart and @rowend

or

WITH MainQuery AS

(
  HUGE SELECT STATEMENT HERE
)
SELECT * INTO #foo

select * from #foo F
CROSS JOIN
(SELECT COUNT(*) AS TotalRows FROM #foo) M
where F.row between @rowstart and @rowend

or

WITH MainQuery AS

(
  HUGE SELECT STATEMENT HERE
)
SELECT * INTO #foo
select * from #foo F where F.row between @rowstart and @rowend

SELECT @outparam = COUNT(*) FROM #foo

Have you heard of OUTPUT parameters? You could store the row count in an OUTPUT parameter and have the stored proc returns the rows.

More info can be found here