分页查询

IFOBJECT_ID(N'dbo.p_show')ISNOTNULL
DROPPROCEDUREdbo.p_show
GO

/**//*--实现分页的通用存储过程

显示指定表、视图、查询结果的第X页
对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法
如果视图或查询结果中有主键,不推荐此方法
如果使用查询语句,而且查询语句使用了orderby,则查询语句必须包含top语句

最后更新时间:2008.01.20
--邹建2003.09(引用请保留此信息)--*/

/**//*--调用示例
EXECdbo.p_show
@QueryStr=N'tb',
@PageSize=5,
@PageCurrent=3,
@FdShow='id,colid,name',
@FdOrder='colid,name'
selectid,colidfromtb
orderbycolid,name


EXECdbo.p_show
@QueryStr=N'
SELECTTOP100PERCENT
*
FROMdbo.sysobjects
ORDERBYxtype',
@PageSize=5,
@PageCurrent=2,
@FdShow='name,xtype',
@FdOrder='xtype,name'
--*/
CREATEPROCdbo.p_show
@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