paging the sqlserver2005 way

Question

paging the sqlserver2005 way

John Q Public-431253

SSC Enthusiast

Points: 164
More actions
April 24, 2007 at 4:54 pm

#175940

Hi,
I'm getting to grips with paging in sqlserver2005. Usually i would create a temp table with a identity column, chuck all my data into that and then use the identity column to choose which records to display.
The handy thing about this is afterwards i can do a select count(*) to find the total number of records in this table and therefore the total number of pages.
So i moved to 2005 all excited about the built-in paging and thought i could get sqlserver to do it all for me. However i can do a initial select from the table it creates but when i try to do a subsequent select to get the total rows it seems like the table has already been dropped. I get the error Invalid object name 'temp_table'
Some sample code:
with ordered_tasks as (
select
row_number() over (
order by case @sort_order
when 'unit_pay_asc' then t.unit_pay
when 'unit_pay_desc' then -t.unit_pay
else t.end_date end asc
) as paging_id
, some more data here...
from my_big_table
)
/*this bit executes ok*/
select
xy and z
from ordered_tasks
/*i would get the error trying to do this:*/
select count(*) as total_records
from ordered_tasks
/code
if i swap the statements around i get the same thing but always on teh second select statement. i can't do anything tricky like combine my select with a assignment to a variable. any suggestions?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

You can only use the CTE in the statement subsequent to it's definition. You can use it more than once within that statement but you can't use it in two different statements.

If you look at Itzik Ben-Gan's book, Inside TSQL Querying, he's got several examples for paging. Here's one:

DECLARE @pagesize as int, @pagenum as int;

SET @pagesize = 5;

SET @pagenum = 2;

WITH SalesCTE AS (SELECT ROW_NUMBER() OVER (ORDER By qty,empId) AS rownum,

empid,mgrid,qty

FROM dbo.Sales)

SELECT rownum,empid,mgrid,tqy

FROM SalesCTE

WHERE rownum > @pagesize * (@pagenum-1)

AND rownum <= @pagesize * @pagenum

ORDER BY rownum;

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

John Q Public-431253 SSC Enthusiast Points: 164 More actions · Answer 2

thanks man,

i've got the paging sorted. i want to get the total number of rows out of the original collection however so i can return the total number of pages in the collection.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Oops. Misunderstood. Sorry.

What about referencing the CTE within a second subselect & get the count that way?

Psuedo-code:

JOIN (SELECT COUNT(*) FROM cteTable) AS t2

That way, it's all part of the statement immediately following the CTE declaration and doesn't violate the usage.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

John Q Public-431253 SSC Enthusiast Points: 164 More actions · Answer 4

great. that's closer to what i want. thanks for your help