邹建的 sql server 分页存储过程 以备用

灰灰鼠精灵

• IFOBJECT_ID(N'dbo.pagination')ISNOTNULL
  
• DROPPROCEDUREdbo.pagination
  
• GO
  
• 
  
• /**//*--实现分页的通用存储过程
  
• 
  
• 显示指定表、视图、查询结果的第X页
  
• 对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法
  
• 如果视图或查询结果中有主键,不推荐此方法
  
• 如果使用查询语句,而且查询语句使用了orderby,则查询语句必须包含top语句
  
• 
  
• 最后更新时间:2008.01.20
  
• --邹建2003.09(引用请保留此信息)--*/
  
• 
  
• /**//*--调用示例
  
• EXECdbo.pagination
  
• @QueryStr=N'tb',
  
• @PageSize=5,
  
• @PageCurrent=3,
  
• @FdShow='id,colid,name',
  
• @FdOrder='colid,name'
  
• selectid,colidfromtb
  
• orderbycolid,name
  
• 
  
• 
  
• EXECdbo.pagination
  
• @QueryStr=N'
  
• SELECTTOP100PERCENT
  
• *
  
• FROMdbo.sysobjects
  
• ORDERBYxtype',
  
• @PageSize=5,
  
• @PageCurrent=2,
  
• @FdShow='name,xtype',
  
• @FdOrder='xtype,name'
  
• --*/
  
• CREATEPROCdbo.pagination
  
• @QueryStrnvarchar(4000),--表名、视图名、查询语句
  
• @PageSizeint=10,--每页的大小(行数)
  
• @PageCurrentint=1,--要显示的页
  
• @FdShownvarchar(4000)=N'',--要显示的字段列表,如果查询结果不需要标识字段,需要指定此值,且不包含标识字段
  
• @FdOrdernvarchar(1000)=N''--排序字段列表
  
• AS
  
• SETNOCOUNTON
  
• DECLARE
  
• @FdNamesysname,--表中的主键或表、临时表中的标识列名
  
• @Id1sysname,--开始和结束的记录号
  
• @Id2sysname,
  
• @Obj_IDint--对象ID
  
• 
  
• --表中有复合主键的处理
  
• DECLARE
  
• @strfdnvarchar(2000),--复合主键列表
  
• @strjoinnvarchar(4000),--连接字段
  
• @strwherenvarchar(2000)--查询条件
  
• 
  
• 
  
• SELECT
  
• @Obj_ID=OBJECT_ID(@QueryStr),
  
• @FdShow=CASE
  
• WHEN@FdShow>N''THENN''+@FdShow
  
• ELSEN'*'
  
• END,
  
• @FdOrder=CASE
  
• WHEN@FdOrder>N''THENN'ORDERBY'+@FdOrder
  
• ELSEN''
  
• END,
  
• @QueryStr=CASE
  
• WHEN@Obj_IDISNULLTHENN'('+@QueryStr+N')A'
  
• ELSEN''+@QueryStr
  
• END
  
• 
  
• --如果显示第一页，可以直接用top来完成
  
• IF@PageCurrent=1
  
• BEGIN
  
• SELECT
  
• @Id1=CAST(@PageSizeasvarchar(20))
  
• EXEC(N'
  
• SELECTTOP'+@Id1+N'
  
• '+@FdShow+N'
  
• FROM'+@QueryStr+N'
  
• '+@FdOrder
  
• )
  
• RETURN
  
• END
  
• 
  
• --如果是表,则检查表中是否有标识更或主键
  
• IF@Obj_IDISNULLOROBJECTPROPERTY(@Obj_ID,'IsTable')=0
  
• GOTOlb_usetemp
  
• ELSE
  
• BEGIN
  
• SELECT
  
• @Id1=CAST(@PageSizeasvarchar(20)),
  
• @Id2=CAST((@PageCurrent-1)*@PageSizeasvarchar(20))
  
• 
  
• --标识列
  
• SELECT
  
• @FdName=name
  
• FROMdbo.syscolumns
  
• WHEREid=@Obj_ID
  
• ANDstatus=0x80
  
• IF@@ROWCOUNT=0--如果表中无标识列,则检查表中是否有主键
  
• BEGIN
  
• DECLARE
  
• @pk_numberint
  
• 
  
• SELECT
  
• @strfd=N'',
  
• @strjoin=N'',
  
• @strwhere=N''
  
• 
  
• SELECT
  
• @strfd=@strfd
  
• +N','+QUOTENAME(name),
  
• @strjoin=@strjoin
  
• +N'ANDA.'+QUOTENAME(name)
  
• +N'=B.'+QUOTENAME(name),
  
• @strwhere=@strwhere
  
• +N'ANDB.'+QUOTENAME(name)+N'ISNULL'
  
• FROM(
  
• SELECT
  
• IX.id,IX.indid,
  
• IXC.colid,ixc.keyno,
  
• C.name
  
• FROMdbo.sysobjectsO,
  
• dbo.sysindexesIX,
  
• dbo.sysindexkeysIXC,
  
• dbo.syscolumnsC
  
• WHEREO.parent_obj=@Obj_ID
  
• ANDO.xtype='PK'
  
• ANDO.name=IX.name
  
• ANDIX.id=@Obj_ID
  
• ANDIX.id=IXC.id
  
• ANDIX.indid=IXC.indid
  
• ANDIXC.id=C.id
  
• ANDIXC.colid=C.colid
  
• )A
  
• ORDERBYkeyno
  
• 
  
• SELECT
  
• @pk_number=@@ROWCOUNT,
  
• @strfd=STUFF(@strfd,1,1,N''),
  
• @strjoin=STUFF(@strjoin,1,5,N''),
  
• @strwhere=STUFF(@strwhere,1,5,N'')
  
• 
  
• IF@pk_number=0
  
• GOTOlb_usetemp--如果表中无主键,则用临时表处理
  
• ELSEIF@pk_number=1
  
• BEGIN
  
• SELECT
  
• @FdName=@strfd
  
• GOTOlb_useidentity--使用单一主键
  
• END
  
• ELSE
  
• GOTOlb_usepk--使用复合主键
  
• END
  
• END
  
• 
  
• /**//*--使用标识列或主键为单一字段的处理方法--*/
  
• lb_useidentity:
  
• EXEC(N'
  
• SELECTTOP'+@Id1+N'
  
• '+@FdShow+N'
  
• FROM'+@QueryStr+N'
  
• WHERE'+@FdName+'NOTIN(
  
• SELECTTOP'+@Id2+N'
  
• '+@FdName+'
  
• FROM'+@QueryStr+N'
  
• '+@FdOrder+N')
  
• '+@FdOrder+N'
  
• ')
  
• RETURN
  
• 
  
• /**//*--表中有复合主键的处理方法--*/
  
• lb_usepk:
  
• EXEC(N'
  
• SELECT
  
• '+@FdShow+N'
  
• FROM(
  
• SELECTTOP'+@Id1+N'
  
• A.*
  
• FROM'+@QueryStr+N'A
  
• LEFTJOIN(
  
• SELECTTOP'+@Id2+N'
  
• '+@strfd+N'
  
• FROM'+@QueryStr+N'
  
• '+@FdOrder+N'
  
• )B
  
• ON'+@strjoin+N'
  
• WHERE'+@strwhere+N'
  
• '+@FdOrder+N'
  
• )A
  
• '+@FdOrder+N'
  
• ')
  
• RETURN
  
• 
  
• /**//*--用临时表处理的方法--*/
  
• lb_usetemp:
  
• SELECT
  
• @FdName=QUOTENAME(N'ID_'+CAST(NEWID()asvarchar(40))),
  
• @Id1=CAST(@PageSize*(@PageCurrent-1)asvarchar(20)),
  
• @Id2=CAST(@PageSize*@PageCurrent-1asvarchar(20))
  
• 
  
• EXEC(N'
  
• SELECT
  
• '+@FdName+N'=IDENTITY(int,0,1),
  
• '+@FdShow+N'
  
• INTO#tb
  
• FROM(
  
• SELECTTOP100PERCENT
  
• *
  
• FROM'+@QueryStr+N'
  
• '+@FdOrder+N'
  
• )A
  
• '+@FdOrder+N'
  
• 
  
• SELECT
  
• '+@FdShow+N'
  
• FROM#tb
  
• WHERE'+@FdName+'BETWEEN'+@Id1+'AND'+@Id2+N'
  
• '
  
• )
  
• GO