CREATE procedure [dbo].[userpage]
(
@SqlWhere varchar(1000)='', --查询条件, 可为空
@pagenum int=20, --每页的记录数
@beginline int=1, --第几页,默认第一页
@SqlTable varchar(1000), --要查询的表或视图,也可以一句sql语句
@SqlColumn varchar(1000), --查询的字段
@SqlPK varchar(50), --主键 必须填写，自动编号字段
@SqlOrder varchar(200) , --排序，可为空，则默认为以主键倒序排列
@GetCount bit=0, --0为取查询结果，1为取查询总数
@totalCount int --0为不限制结果总数，大于0即为取@totalCount条数据，其他忽视
)
as
set nocount on
declare @PageLowerBound int
declare @PageUpperBound int
declare @sqlstr nvarchar(2000)
declare @d datetime
if @SqlWhere=''
begin
set @SqlWhere=' where 1=1 '
end
else
begin
set @SqlWhere=' where 1=1 and '+@SqlWhere+' '
end
if @SqlOrder=''
begin
set @SqlOrder=' order by '+@SqlPK+' desc'
end
else
begin
set @SqlOrder=' order by '+@SqlOrder
end
if @SqlColumn='' set @SqlColumn=' * '
if @beginline=0 set @beginline=1
if @totalcount>0
begin
declare @totalpage int
set @totalpage=@totalcount/@pagenum
if @totalcount%@pagenum >0 set @totalpage=@totalpage+1
if @beginline>@totalpage set @beginline=@totalpage
end
declare @mytop nvarchar(20)
set @mytop=''
if @totalcount>0 set @mytop=' top '+convert(nvarchar(10),@totalcount)+' '
IF @GetCount=1
BEGIN
declare @Count int
declare @sCount int
if @totalCount=0
begin
set @sqlstr=N'select @sCount=count(1) FROM '+@SqlTable+@SqlWhere
end
else
begin
set @sqlstr=N'select @sCount=count(1) FROM '+@SqlTable+' where '+@SqlPK+' in (select top '+convert(nvarchar(10),@totalcount)+' '+@SqlPK+' from '+@SqlTable+' '+@Sqlwhere+')'
end
Exec sp_executesql @sqlstr,N'@sCount int outPut',@Count output
select @count as totalCount
END
else
begin
if @beginline=1
begin
set @sqlstr='select '+@SqlColumn+' from '+@SqlTable+' where '+@sqlPK+' in (select top '+str(@pagenum)+' '+@SqlPK+' from '+@SqlTable+@SqlWhere+@SqlOrder+')'+@SqlOrder
Exec sp_executesql @sqlstr
set nocount off
end
else
begin
set @PageLowerBound=(@beginline-1)*@pagenum
set @PageUpperBound=@PageLowerBound+@pagenum
create table #pageindex(temporary_id int identity(1,1) not null,temporary_nid int)
create unique clustered index index_nid_pageindex on #pageindex(temporary_id)
set rowcount @PageUpperBound
set @sqlstr=N'insert into #pageindex(temporary_nid) select '+@mytop+@SqlPK+' from '+@SqlTable+@SqlWhere+@SqlOrder
Exec sp_executesql @sqlstr
set @sqlstr='select '+@SqlColumn+' FROM '+ @SqlTable +' inner join #pageindex p on '+@SqlPK+'=p.temporary_nid and (p.temporary_id>'+STR(@PageLowerBound)+') and (p.temporary_id <='+STR(@PageUpperBound)+')' +@SqlOrder
Exec sp_executesql @sqlstr
set nocount off
drop table #pageindex
end
end
GO
|