28,409
社区成员




if not object_id('Cdt_changePage') is null drop procedure Cdt_changePage
go
create procedure [dbo].[Cdt_changePage]
@pagesize int, --每页显示的数据数
@pageindex int, --当前页码
@tbName varchar(20), --表名
@columns varchar(600), --需要查询的字段
@keyIndex varchar(20), --自增字段名(自动编号)
@where varchar(200), --查询条件,不用写where
@order varchar(200) --排序,不用写order by
as
declare @strSQL varchar(3000)
--准备分页
declare @datacount int --总记录数
declare @pagecount int --总页数
if @order=''
begin
set @order=' '+@keyIndex+' desc'
end
--获取总记录数
if not object_id('#cdt_tem') is null drop table #cdt_tem
create table dbo.#cdt_tem(datacount int)
exec('insert #cdt_tem select count(*) as datacount from '+@tbName+' where 1=1 '+@where)
set @datacount=(select top 1 datacount from #cdt_tem)
if not object_id('#cdt_tem') is null drop table #cdt_tem
--计算总页数
if(@datacount%@pagesize)=0
begin
set @pagecount=(@datacount/@pagesize)
end
else
begin
set @pagecount=(@datacount/@pagesize)+1
end
--开始分页
if @pageindex<=1
begin
set @strSQL = 'select top '+str(@pagesize)+' '+str(@datacount)+' as datacount, '+str(@pagecount)+' as pagecount, '+@columns+'
from '+@tbName+' where 1=1 '+@where+' order by '+@order
end
else
begin
set @strSQL = 'select top '+str(@pagesize)+' '+str(@datacount)+' as datacount, '+str(@pagecount)+' as pagecount, '+@columns+'
from '+@tbName+' where '+@keyIndex+'
not in(select top '+str((@pageindex-1)*@pagesize)+' '+@keyIndex+'
from '+@tbName+' where 1=1 '+@where+' order by '+@order+')
'+@where+' order by '+@order
end
exec (@strSQL)
go
exec Cdt_changePage 10,1,'chinaarea','*','Id','','Id asc'