分頁SQLServer存儲過程

　　/*用存儲過程實現的分頁程序

　　顯示指定表視圖查詢結果的第X頁

　　對於表中主鍵或標識列的情況直接從原表取數查詢其它情況使用臨時表的方法

　　如果視圖或查詢結果中有主鍵不推薦此方法*/

　　/*調用示例

　　exec p_show 地區資料

　　exec p_show 地區資料地區編號地區名稱助記碼地區編號

　　*/

　　/*

　　因為要顧及通用性所以對帶排序的查詢語句有一定要求如果先排序再出結果就是:

　　exec p_show select top percent * from 地區資料 order by 地區名稱地區編號地區名稱助記碼地區名稱

　　查詢語句加上:top percent //top時

　　*/

　　if exists (select * from dbosysobjects where id = object_id(N[dbo][p_show]) and OBJECTPROPERTY(id NIsProcedure) = )

　　drop procedure [dbo][p_show]

　　GO

　　CREATE Proc p_show

　　@QueryStr nvarchar() 表名視圖名查詢語句

　　@PageSize int= 每頁的大小(行數)

　　@PageCurrent int= 要顯示的頁

　　@FdShow nvarchar ()= 要顯示的字段列表如果查詢結果有標識字段需要指定此值且不包含標識字段

　　@FdOrder nvarchar ()= 排序字段列表

　　as

　　declare @FdName nvarchar() 表中的主鍵或表臨時表中的標識列名

　　@Id varchar()@Id varchar() 開始和結束的記錄號

　　@Obj_ID int 對象ID

　　表中有復合主鍵的處理

　　declare @strfd nvarchar() 復合主鍵列表

　　@strjoin nvarchar() 連接字段

　　@strwhere nvarchar() 查詢條件

　　select @Obj_ID=object_id(@QueryStr)

　　@FdShow=case isnull(@FdShow) when then * else

　　[url=mailto:+@FdShow]+@FdShow[/url]

　　end

　　@FdOrder=case isnull(@FdOrder) when then else order by

　　[url=mailto:+@FdOrder]+@FdOrder[/url]

　　end

　　@QueryStr=case when @Obj_ID is not null then

　　[url=mailto:+@QueryStr]+@QueryStr[/url]

　　else (

　　[url=mailto:+@QueryStr+]+@QueryStr+[/url]

　　) a end

　　如果顯示第一頁可以直接用top來完成

　　if @PageCurrent=

　　begin

　　select @Id=cast(@PageSize as varchar())

　　exec(select top

　　[url=mailto:+@Id+@FdShow+]+@Id+@FdShow+[/url]

　　from

　　[url=mailto:+@QueryStr+@FdOrder]+@QueryStr+@FdOrder[/url]

　　)

　　return

　　end

　　如果是表則檢查表中是否有標識更或主鍵

　　if @Obj_ID is not null and objectproperty(@Obj_IDIsTable)=

　　begin

　　select @Id=cast(@PageSize as varchar())

　　@Id=cast((@PageCurrent)*@PageSize as varchar())

　　select @FdName=name from syscolumns where

　　[url=mailto:id=@Obj_ID]id=@Obj_ID[/url]

　　and status=x

　　if @@rowcount= 如果表中無標識列則檢查表中是否有主鍵

　　begin

　　if not exists(select from sysobjects where

　　[url=mailto:parent_obj=@Obj_ID]parent_obj=@Obj_ID[/url]

　　and xtype=PK)

　　goto lbusetemp 如果表中無主鍵則用臨時表處理

　　select @FdName=name from syscolumns where

　　[url=mailto:id=@Obj_ID]id=@Obj_ID[/url]

　　and colid in(

　　select colid from sysindexkeys where @Obj_ID=id and indid in(

　　select indid from sysindexes where @Obj_ID=id and name in(

　　select name from sysobjects where xtype=PK and

　　[url=mailto:parent_obj=@Obj_ID]parent_obj=@Obj_ID[/url]

　　)))

　　if @@rowcount> 檢查表中的主鍵是否為復合主鍵

　　begin

　　select @strfd=@strjoin=@strwhere=

　　select @strfd=@strfd+[+name+]

　　@strjoin=@strjoin+ and a[+name+]=b[+name+]

　　@strwhere=@strwhere+ and b[+name+] is null

　　from syscolumns where

　　[url=mailto:id=@Obj_ID]id=@Obj_ID[/url]

　　and colid in(

　　select colid from sysindexkeys where @Obj_ID=id and indid in(

　　select indid from sysindexes where @Obj_ID=id and name in(

　　select name from sysobjects where xtype=PK and

　　[url=mailto:parent_obj=@Obj_ID]parent_obj=@Obj_ID[/url]

　　)))

　　select @strfd=substring(@strfd)

　　@strjoin=substring(@strjoin)

　　@strwhere=substring(@strwhere)

　　goto lbusepk

　　end

　　end

　　end

　　else

　　goto lbusetemp

　　/*使用標識列或主鍵為單一字段的處理方法*/

　　lbuseidentity:

　　exec(select top

　　[url=mailto:+@Id+@FdShow+]+@Id+@FdShow+[/url]

　　from

　　[url=mailto:+@QueryStr]+@QueryStr[/url]

　　+ where

　　[url=mailto:+@FdName+]+@FdName+[/url]

　　not in(select top

　　[url=mailto:+@Id+]+@Id+[/url]

　　[url=mailto:+@FdName+]+@FdName+[/url]

　　from

　　[url=mailto:+@QueryStr+@FdOrder]+@QueryStr+@FdOrder[/url]

　　+)+@FdOrder

　　)

　　return

　　/*表中有復合主鍵的處理方法*/

　　lbusepk:

　　exec(select

　　[url=mailto:+@FdShow+]+@FdShow+[/url]

　　from(select top

　　[url=mailto:+@Id+]+@Id+[/url]

　　a* from

　　(select top percent * from

　　[url=mailto:+@QueryStr+@FdOrder+]+@QueryStr+@FdOrder+[/url]

　　) a

　　left join (select top

　　[url=mailto:+@Id+]+@Id+[/url]

　　[url=mailto:+@strfd+]+@strfd+[/url]

　　from

　　[url=mailto:+@QueryStr+@FdOrder+]+@QueryStr+@FdOrder+[/url]

　　) b on

　　[url=mailto:+@strjoin+]+@strjoin+[/url]

　　where

　　[url=mailto:+@strwhere+]+@strwhere+[/url]

　　) a

　　)

　　return

　　/*用臨時表處理的方法*/

　　lbusetemp:

　　select @FdName=[ID_+cast(newid() as varchar())+]

　　@Id=cast(@PageSize*(@PageCurrent) as varchar())

　　@Id=cast(@PageSize*@PageCurrent as varchar())

　　exec(select

　　[url=mailto:+@FdName+=identity(int)+@FdShow+]+@FdName+=identity(int)+@FdShow+[/url]

　　into #tb

　　[url=mailto:from+@QueryStr+@FdOrder+]from+@QueryStr+@FdOrder+[/url]

　　select

　　[url=mailto:+@FdShow+]+@FdShow+[/url]

　　from #tb where

　　[url=mailto:+@FdName+]+@FdName+[/url]

　　between

　　[url=mailto:+@Id+]+@Id+[/url]

　　and

　　[url=mailto:+@Id]+@Id[/url]

　　)

　　GO

From:http://tw.wingwit.com/Article/program/SQLServer/201311/22063.html