--分页存储过程 把表进行分页(单表的分页传表名) create proc Pageproc ( @TableName varchar(50), --表名 @pagecount int, --当前页索引 @pagesize int, --每页显示的行数 @Condition varchar(50), --查询条件 @paixu varchar(50), --排序 @zongye int output --总行数 ) as --获取总行数 declare @zonghang int; declare @getcountsql nvarchar(500) set @getcountsql='select @a=count(*) from '+@TableName+' ' exec sp_executesql @getcountsql,N'@a int output',@zonghang output --把执行的结果赋给变量@zonghang declare @strSql nvarchar(500) set @strSql='select top '+convert(varchar,@pagesize)+' * from '+@TableName+' '; begin if @pagecount!=0 begin set @strSql=@strSql+' where id>(select max(id) from (select top '+Convert(varchar,@pagesize*@pagecount)+' id from '+@TableName+' order by id asc) as aa)' end if @Condition!='' begin set @strSql='select * from ('+(@strSql)+') as bb where 1=1 '+@Condition +' '; end set @strSql='select * from ('+@strSql+' order by id asc ) as bb' +' order by '+@paixu+' desc'; exec sp_executesql @strSql; end begin if @zonghang%@pagesize!=0 begin set @zongye=(@zonghang/@pagesize)+1; end else begin set @zongye=@zonghang/@pagesize; end end --表 create table testTable ( id identity(1,1) primary key not null, username varchar(20) not null ) go --给表中插入测试数据 declare @i int set @i=0 while(@i<1000) begin insert into testTable (username) values ('aa'+Convert(varchar,@i)) set @i=@i+1 end --测试 declare @aa int exec Pageproc 'testTable',0,10,'','id',@aa output select @aa --删除 drop proc pageproc