分页 SQLServer存储过程

/*--用存储过程实现的分页程序
显示指定表、视图、查询结果的第X页
对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法
如果视图或查询结果中有主键,不推荐此方法
--邹建?2003.09--*/
/*--调用示例
exec?p_show?'地区资料'
exec?p_show?'地区资料',5,3,'地区编号,地区名称,助记码','地区编号'
--*/
/*
因为要顾及通用性,所以对带排序的查询语句有一定要求.如果先排序,再出结果.就是:
exec?p_show?'select?top?100?percent?*?from?地区资料?order?by?地区名称',5,3,'地区编号,地区名称,助记码','地区名称'
--查询语句加上:top?100?percent?//top时
*/
if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'[dbo].[p_show]')?and?OBJECTPROPERTY(id,?N'IsProcedure')?=?1)
drop?procedure?[dbo].[p_show]
GO
Create?Proc?p_show
@QueryStr?nvarchar(4000),?--表名、视图名、查询语句
@PageSize?int=10,?--每页的大小(行数)
@PageCurrent?int=1,?--要显示的页
@FdShow?nvarchar?(4000)='',?--要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder?nvarchar?(1000)=''?--排序字段列表
as
declare?@FdName?nvarchar(250)?--表中的主键或表、临时表中的标识列名
,@Id1?varchar(20),@Id2?varchar(20)?--开始和结束的记录号
,@Obj_ID?int?--对象ID
--表中有复合主键的处理
declare?@strfd?nvarchar(2000)?--复合主键列表
,@strjoin?nvarchar(4000)?--连接字段
,@strwhere?nvarchar(2000)?--查询条件

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=1?
begin
select?@Id1=cast(@PageSize?as?varchar(20))
exec('select?top?'+@Id1+@FdShow+'?from?'+@QueryStr+@FdOrder)
return
end
--如果是表,则检查表中是否有标识更或主键
if?@Obj_ID?is?not?null?and?objectproperty(@Obj_ID,'IsTable')=1
begin
select?@Id1=cast(@PageSize?as?varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize?as?varchar(20))
select?@FdName=name?from?syscolumns?where?id=@Obj_ID?and?status=0x80
if?@@rowcount=0?--如果表中无标识列,则检查表中是否有主键
begin
if?not?exists(select?1?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>1?--检查表中的主键是否为复合主键
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,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto?lbusepk
end
end
end
else
goto?lbusetemp
/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:?
exec('select?top?'+@Id1+@FdShow+'?from?'+@QueryStr
+'?where?'+@FdName+'?not?in(select?top?'
+@Id2+'?'+@FdName+'?from?'+@QueryStr+@FdOrder
+')'+@FdOrder
)
return
/*--表中有复合主键的处理方法--*/
lbusepk:?
exec('select?'+@FdShow+'?from(select?top?'+@Id1+'?a.*?from
(select?top?100?percent?*?from?'+@QueryStr+@FdOrder+')?a
left?join?(select?top?'+@Id2+'?'+@strfd+'?
from?'+@QueryStr+@FdOrder+')?b?on?'+@strjoin+'
where?'+@strwhere+')?a'
)
return
/*--用临时表处理的方法--*/
lbusetemp:?
select?@FdName='[ID_'+cast(newid()?as?varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1)?as?varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1?as?varchar(20))
exec('select?'+@FdName+'=identity(int,0,1),'+@FdShow+'
into?#tb?from'+@QueryStr+@FdOrder+'
select?'+@FdShow+'?from?#tb?where?'+@FdName+'?between?'
+@Id1+'?and?'+@Id2
)
GO

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有