/*用存储过程实现的分页程序
显示指定表视图查询结果的第X页
对于表中主键或标识列的情况直接从原表取数查询其它情况使用临时表的方法
如果视图或查询结果中有主键不推荐此方法*/
/*调用示例
exec p_show 地区资料
exec p_show 地区资料地区编号地区名称助记码地区编号
*/
/*
因为要顾及通用性所以对带排序的查询语句有一定要求如果先排序再出结果就是:
exec p_show select top percent * from 地区资料 order by 地区名称地区编号地区名称助记码地区名称
查询语句加上:top percent //top时
*/
if exists (select * from dbosysobjects where id = object_id(N[dbo][p_show]) and OBJECTPROPERTY(id NIsProcedure) = )
drop procedure [dbo][p_show]
GO
CREATE Proc p_show
@QueryStr nvarchar() 表名视图名查询语句
@PageSize int= 每页的大小(行数)
@PageCurrent int= 要显示的页
@FdShow nvarchar ()= 要显示的字段列表如果查询结果有标识字段需要指定此值且不包含标识字段
@FdOrder nvarchar ()= 排序字段列表
as
declare @FdName nvarchar() 表中的主键或表临时表中的标识列名
@Id varchar()@Id varchar() 开始和结束的记录号
@Obj_ID int 对象ID
表中有复合主键的处理
declare @strfd nvarchar() 复合主键列表
@strjoin nvarchar() 连接字段
@strwhere nvarchar() 查询条件
select @Obj_ID=object_id(@QueryStr)
@FdShow=case isnull(@FdShow) when then * else
+@FdShow
end
@FdOrder=case isnull(@FdOrder) when then else order by
+@FdOrder
end
@QueryStr=case when @Obj_ID is not null then
+@QueryStr
else (
+@QueryStr+
) a end
如果显示第一页可以直接用top来完成
if @PageCurrent=
begin
select @Id=cast(@PageSize as varchar())
exec(select top
+@Id+@FdShow+
from
+@QueryStr+@FdOrder
)
return
end
如果是表则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_IDIsTable)=
begin
select @Id=cast(@PageSize as varchar())
@Id=cast((@PageCurrent)*@PageSize as varchar())
select @FdName=name from syscolumns where
id=@Obj_ID
and status=x
if @@rowcount= 如果表中无标识列则检查表中是否有主键
begin
if not exists(select from sysobjects where
parent_obj=@Obj_ID
and xtype=PK)
goto lbusetemp 如果表中无主键则用临时表处理
select @FdName=name from syscolumns where
id=@Obj_ID
and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype=PK and
parent_obj=@Obj_ID
)))
if @@rowcount> 检查表中的主键是否为复合主键
begin
select @strfd=@strjoin=@strwhere=
select @strfd=@strfd+[+name+]
@strjoin=@strjoin+ and a[+name+]=b[+name+]
@strwhere=@strwhere+ and b[+name+] is null
from syscolumns where
id=@Obj_ID
and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype=PK and
parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd)
@strjoin=substring(@strjoin)
@strwhere=substring(@strwhere)
goto lbusepk
end
end
end
else
goto lbusetemp
/*使用标识列或主键为单一字段的处理方法*/
lbuseidentity:
exec(select top
+@Id+@FdShow+
from
+@QueryStr
+ where
+@FdName+
not in(select top
+@Id+
+@FdName+
from
+@QueryStr+@FdOrder
+)+@FdOrder
)
return
/*表中有复合主键的处理方法*/
lbusepk:
exec(select
+@FdShow+
from(select top
+@Id+
a* from
(select top percent * from
+@QueryStr+@FdOrder+
) a
left join (select top
+@Id+
+@strfd+
from
+@QueryStr+@FdOrder+
) b on
+@strjoin+
where
+@strwhere+
) a
)
return
/*用临时表处理的方法*/
lbusetemp:
select @FdName=[ID_+cast(newid() as varchar())+]
@Id=cast(@PageSize*(@PageCurrent) as varchar())
@Id=cast(@PageSize*@PageCurrent as varchar())
exec(select
+@FdName+=identity(int)+@FdShow+
into #tb
from+@QueryStr+@FdOrder+
select
+@FdShow+
from #tb where
+@FdName+
between
+@Id+
and
+@Id
)
GO
