Problems with the SELECT ROW_NUMBER() OVER approach

Hi guys, I am using this approach in order to be able to do server side paging on the SQL server (why Microsoft, why can't you just put the limit vlause to work?).

Anyway, here is what I am using...

http://davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx

My code is somehow structured like this:

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Day, Month, Year DESC)AS Row, *
FROM reptable
where $LOTS OF CLAUSES$
)

select Year, Month, Day, $ALL MY DATA$ from LogEntries
where Row between (@.PageIndex - 1) * @.PageSize + 1 and @.PageIndex*@.PageSize

This works like a CHARM.

Problem is I've placed this under a stored procedure and I need the procedure to return the total records affected by the first query (the total rows returned on that query).

But, if I use set @.parameter=@.@.ROWCOUNT after the select, it gives me only 10 (which is the pagesize)

If I use set @.parameter=@.@.ROWCOUNT before the select, SQL says it is not valid (I guess it waits for a SELECT after a WITH Clause).

I tried select count(*) from LogEntries after the select clause. Still no success. It says the table doesn't exist. I guess the object looses his value after the select is executed.

What can I do in order to make this work?

Any help is appreciated

Luis

What about:

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Day, Month, Year DESC)AS Row, *
FROM reptable
where $LOTS OF CLAUSES$
)

select Year, Month, Day, (SELECT MAX(Row) FROM LogEntries) ,$ALL MY DATA$ from LogEntries
where Row between (@.PageIndex - 1) * @.PageSize + 1 and @.PageIndex*@.PageSize

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Or use:

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Day, Month, Year DESC)AS Row, COUNT(*) OVER() as cnt, *
FROM reptable
where $LOTS OF CLAUSES$
)
select Year, Month, Day, cnt ,$ALL MY DATA$ from LogEntries
where Row between (@.PageIndex - 1) * @.PageSize + 1 and @.PageIndex*@.PageSize

--Jens' idea will work too, but I think putting it in the CTE will do it in less passes of the data.

Rob|||I am sorry guys (stupid mistake). I forgot to mention that the count(*) cannot come on the SQL column list because this list is used on a auto-generated report and there is no way of removing it.
So, the count should be returned in a output parameter of the procedure. It can't be on the column list.|||Ah... well, if you're not getting many rows for your report, you could just populate a temporary table with all the columns you want (including the count), and then do something like:

declare @.thecount int
select top (1) @.thecount = cnt
from #thetemptable

select col1, col2 --, etc (but not cnt)
from #thetemptable

drop table #thetemptable|||The problem is that the report is usually big (more then 1.000 rows) and I am afraid temporary tables will cause a delay on the process. Do you think it is a good idea to use temporary tables for that?

Is there any other way of getting this value out?|||Well, if you can't include it in your main query, then you will have to requery it somewhere. Since you're only producing a page of data at a time, I think a temporary table might not be a bad option.

Otherwise you'll have to re-run your query, with a count(*) in place of the select fields. Might be just fine, but you'll have to test it to see.

Rob