邹建的 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
页:
[1]