using rowcount in a nested query

[Earth]

I am able to run a query that returns n rows, within a specified date range (one row for each date). I want to be able to recall this same query and get either the "next" n rows within that same specified date range, or the "previous" n rows.

The rows need to be presented ASC by Date.

This is the query atm:

<snip>
CREATE PROCEDURE dbo.sp_BalancesAsAtAggs
@dateFrom datetime,
@dateTo datetime,
@dateEdge datetime,
@direction varchar(10),
@numRows int
AS

IF (@direction = 'Next')

BEGIN

SET ROWCOUNT @numRows
SELECT (Some select stuff that works and aggregates data based on date - returns one row for each date)
FROM tblBalancesAsAt
WHERE convert(datetime, left(AsAtDate, 11)) >= convert(varchar, left(@dateFrom,11))
and convert(datetime, left(AsAtDate, 11)) <=convert(varchar, left(@dateTo,11))
and convert(datetime, left(AsAtDate, 11)) < convert(varchar, left(@dateEdge,11))

GROUP BY AsAtDate
ORDER BY AsAtDate ASC

END

ELSE
IF (@direction= 'Previous')

BEGIN

SELECT *
FROM ( SET ROWCOUNT @numRows
SELECT (As above)
FROM tblBalancesAsAt
WHERE convert(datetime, left(AsAtDate, 11)) >= convert(varchar, left(@dateFrom,11))
and convert(datetime, left(AsAtDate, 11)) <=convert(varchar, left(@dateTo,11))
and convert(datetime, left(AsAtDate, 11)) < convert(varchar, left(@dateEdge,11))

GROUP BY AsAtDate
ORDER BY AsAtDate DESC
)
ORDER BY AsAtDate DESC

END

GO
</snip>


Currently, I have &quot;Next&quot; working. The PROBLEM is:
I can't seem to use &quot;SET ROWCOUNT&quot; within the SELECT statement. I have tried executing the inner query separately - ie. if I run it separately it works, but I can't exec it from within the FROM clause (ie. effectively nesting).

Any suggestions?

 

[tlbroadbent]

As you have found, you can't set rowcount in a query as you are trying. Check the code at the following link for a method of paging in a SQL stored procedure.

http://www.aspfaqs.com/webtech/062899-1.shtml

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[Earth]

Thanks for the suggestion Broadbent. I'm attempting to find a client-side sort solution for flexibility - ie. so that I don't need to determine the number of pages before I start to navigate through. However, if I end up coming back to this paging solution, I'll post and let you know how it goes.