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

azheng270

浏览: 92942 次
性别:
来自: 杭州

最近访客更多访客>>

4351901

hitgnu

belldeep

xlwn

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (68)

社区版块

存档分类

SQL Server SQL Go C C++

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