--把传入的sql 语句进行分页显示 可以排序 create proc PageprocBySql ( @strSQL NVARCHAR(500), @pageIndex int, --当前页索引 @pagesize int, --每页显示的行数 @paixu varchar(50), --排序 @pagecount int output --总行数 ) as --获取总行数 declare @zonghang int; declare @getcountsql nvarchar(500) set @getcountsql='select @a=count(*) from ('+@strSQL+') as aa' exec sp_executesql @getcountsql,N'@a int output',@zonghang output --把执行的结果赋给变量@zonghang declare @SQL nvarchar(500) begin if @pageIndex!=0 begin if(@paixu!='') begin set @SQL='select * from (select top '+convert(varchar,@pagesize*@pageIndex)+' * from (select * from ('+@strSQL+') as cc where id> (select max(id) from (select top '+Convert(varchar,@pagesize*@pageIndex)+' id from ('+@strSQL+') as aa order by id asc ) as bb)) as ee order by id asc) as vv order by '+@paixu+' desc'; end else begin set @SQL='select top '+convert(varchar,@pagesize*@pageIndex)+' * from (select * from ('+@strSQL+') as cc where id> (select max(id) from (select top '+Convert(varchar,@pagesize*@pageIndex)+' id from ('+@strSQL+') as aa order by id asc ) as bb)) as ee'; end end else begin if(@paixu!='') begin set @SQL='select top '+convert(varchar,@pagesize)+' * from (select top '+convert(varchar,@pagesize)+' * from ('+@strSQL+') as aa order by id asc) as bb order by '+@paixu+' desc'; end else begin set @SQL='select top '+convert(varchar,@pagesize)+' * from ('+@strSQL+') as aa '; end end exec sp_executesql @SQL end begin if @zonghang%@pagesize!=0 begin set @pagecount=(@zonghang/@pagesize)+1; end else begin set @pagecount=@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 pageprocbysql 'select * from testTable',11,10,'id',@aa output select @aa --删除 drop proc pageprocbysql