熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

分頁SQLServer存儲過程

2013-11-15 14:37:07  來源: SQL Server 

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

  顯示指定表視圖查詢結果的第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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.