/*--用存儲過程實現的分頁程序顯示指定表、視圖、查詢結果的第X頁對于表中主鍵或標識列的情況,直接從原表取數查詢，其它情況使用臨時表的方法如果視圖或查詢結果中有主鍵,不推薦此方法--鄒建 2003.09--*//*--調用示例exec p_show '地區資料'exec p_show '地區資料',5,3,'地區編號,地區名稱,助記碼','地區編號'--*//*因為要顧及通用性,所以對帶排序的查詢語句有一定要求.如果先排序,再出結果.就是:exec p_show 'select top 100 percent * from 地區資料 order by 地區名稱',5,3,'地區編號,地區名稱,助記碼','地區名稱'--查詢語句加上:top 100 percent //top時*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[p_show]GOCREATE Proc p_show@QueryStr nvarchar(4000),--表名、視圖名、查詢語句@PageSize int=10,--每頁的大小(行數)@PageCurrent int=1,--要顯示的頁@FdShow nvarchar (4000)='',--要顯示的字段列表,如果查詢結果有標識字段,需要指定此值,且不包含標識字段@FdOrder nvarchar (1000)=''--排序字段列表asdeclare @FdName nvarchar(250)--表中的主鍵或表、臨時表中的標識列名,@Id1 varchar(20),@Id2 varchar(20)--開始和結束的記錄號,@Obj_ID int--對象ID--表中有復合主鍵的處理declare @strfd nvarchar(2000)--復合主鍵列表,@strjoin nvarchar(4000)--連接字段,@strwhere nvarchar(2000)--查詢條件select @Obj_ID=object_id(@QueryStr),@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end--如果顯示第一頁，可以直接用top來完成if @PageCurrent=1beginselect @Id1=cast(@PageSize as varchar(20))exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)returnend--如果是表,則檢查表中是否有標識更或主鍵if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1beginselect @Id1=cast(@PageSize as varchar(20)),@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))select @FdName=name from syscolumns where id=@Obj_ID and status=0x80if @@rowcount=0--如果表中無標識列,則檢查表中是否有主鍵beginif not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')goto lbusetemp--如果表中無主鍵,則用臨時表處理select @FdName=name from syscolumns where id=@Obj_ID 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 parent_obj=@Obj_ID)))if @@rowcount>1--檢查表中的主鍵是否為復合主鍵beginselect @strfd='',@strjoin='',@strwhere=''select @strfd=@strfd+',['+name+']',@strjoin=@strjoin+' and a.['+name+']=b.['+name+']',@strwhere=@strwhere+' and b.['+name+'] is null'from syscolumns where id=@Obj_ID 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 parent_obj=@Obj_ID)))select @strfd=substring(@strfd,2,2000),@strjoin=substring(@strjoin,5,4000),@strwhere=substring(@strwhere,5,4000)goto lbusepkendendendelsegoto lbusetemp/*--使用標識列或主鍵為單一字段的處理方法--*/lbuseidentity:exec('select top '+@Id1+@FdShow+' from '+@QueryStr+' where '+@FdName+' not in(select top '+@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder+')'+@FdOrder)return/*--表中有復合主鍵的處理方法--*/lbusepk:exec('select '+@FdShow+' from(select top '+@Id1+' a.* from(select top 100 percent * from '+@QueryStr+@FdOrder+') aleft join (select top '+@Id2+' '+@strfd+' from '+@QueryStr+@FdOrder+') b on '+@strjoin+'where '+@strwhere+') a')return/*--用臨時表處理的方法--*/lbusetemp:select @FdName='[ID_'+cast(newid() as varchar(40))+']',@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20)),@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'into #tb from'+@QueryStr+@FdOrder+'select '+@FdShow+' from #tb where '+@FdName+' between '+@Id1+' and '+@Id2)GO

關鍵字：SQL Server、存儲過程