sql server new users: Best way to reuse query result? (in a paging scenario) -- sql server new users

Best way to reuse query result? (in a paging scenario)

info NO[at]SPAM programmersheaven.com
11/22/2006 3:20:24 PM

sql server new users:

Hi

Using SQL Server 2005.

I have a fairly complex query that I apply paging to using the With
statement like

(dummy sample taken from
http://davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx)

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)AS Row, Date, Description
FROM LOG WHERE xxxxxxxxxxxxx)

SELECT Date, Description
FROM LogEntries
WHERE Row between @RowStart and @RowEnd

That's fine.

but to be able to figure out the number of pages, I also need to know
the total # of rows in LogEntries above.

like

SELECT Count(*) FROM LogEntries

But the scope of the "LogEntries" result is only one query and I cant
apply two SELECT statements against that result.

So, are there any better way than having to run the same query twice?
like

SELECT Count(*) FROM LOG WHERE xxxxxxxxxxxxx

Do I have to use SQLCLR to fix this? like doing the query once in
SQLCLR and output two recordsets from the SQLCLR procedure?

Re: Best way to reuse query result? (in a paging scenario)

Arnie Rowland
11/22/2006 3:26:04 PM

Here is one solution for Paging queries, perhaps it will give you a clue.

Paging Queries
www.aspfaq.com/2120

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

[quoted text, click to view]