paging the sqlserver2005 way
-
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?
-
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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
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.
-
Oops. Misunderstood. Sorry.
What about referencing the CTE within a second subselect & get the count that way?
Psuedo-code:
<paging logic>
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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
great. that's closer to what i want. thanks for your help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply