邹建的 sql server 分页存储过程 以备用 SQL ServerSQLGoCC++
程序员文章站
2024-02-16 19:21:22
...
- 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