利用CTE將分頁資料取出-Stored Procedure篇

之前在 用CTE來取出指定筆數 – SQL2005 這篇介紹了如何利用 CTE 來取出指定的筆數, 這篇文章將進一步實作一支 stored procedure 做為方便分頁的方式.

主要是為了方便取出指定的筆數來實作的, 所以傳入的參數很單純, 就是原本的查詢指令, 將查詢及排序條件分開, 並指定開始的索引值和結束的索引值即可, 建立的程式碼如下:

create proc sp_getRecordByCTE 
@sqlcmd nvarchar(4000),
@ordercmd nvarchar(1000),
@startindex int,
@endindex int
as
declare @mycmd nvarchar(4000)
select @mycmd = '
with myctequery as (
select row_number() over ( ' + @ordercmd + ' ) 
as CTESN, '+replace(@sqlcmd,'select ',' ')+' )
select * from myctequery 
where CTESN between ' + convert(nvarchar,@startindex) + ' and '+convert(nvarchar,@endindex) 
exec sp_executesql @mycmd

其中比較特別的是為了方便輸入起見, 只要將原來查詢指令的非排序部分和排序部分分開即可, 簡單範列如下:

原來的查詢是:

select a.orderid, a.orderdate, b.custname, b.custaddr from orders a inner join cust b on a.ordercustid = b.custid order by a.orderdate desc

利用上面的 stored procedure 做指定分頁的筆數若為 51~100 (每頁50筆的第二頁), 先將指令區分為紅色查詢及藍色排序, 並且如下輸入給 sp_getRecordByCTE:

exec sp_getRecordByCTE
'select a.orderid, a.orderdate, b.custname, b.custaddr from orders a inner join cust b on a.ordercustid = b.custid',
'order by a.orderdate desc',
51,100

便能將指定的分頁第二頁(51~100)這些資料取出, 這樣和使用 ADO 分頁比較, 不僅可以降低 network IO 外, 更能在複雜查詢時提升效能, 減少不必要的資源浪費, 對於網頁分頁使用時有很大的幫助. 不過目前尚有一個問題無法解決, 就是總筆數這個部分, 因為利用 ADO 查詢時, 可以取得總筆數這個資料, 利用這個 stored procedure sp_getRecordByCTE 將無法取得這個資訊, 看是否有沒有什麼好方法來做這個資料的取得及回傳, 若各位有什麼好建議也歡迎給我建議!

希望這個 stored procedure 對各位能有所幫助.

繼續閱讀:

• 存储过程分页实现代码，2005 T-SQL新增功能 http://www.cnblogs.com/microant/archive/2007/07/05/807565.html
• 分页实现方法的性能比较 http://blog.csdn.net/yizhu2000/archive/2007/06/03/1636573.aspx
• 如何实现SQL Server 2005快速web分页 http://www.no-sky.com/databases/2/2007-01-25/15762.html