通用分页存储过程高效动态表
if exists(select * from sysobjects where name='pagedQueryProc')begindrop proc pagedQueryProcprint 'exists'endelse print 'not exists'gocreate proc pagedQueryProc@pageIndex int,@pageSize int ,@tableName varchar(30)asdeclare @strSql nvarchar(3000);declare @columnsName varchar(30);declare @totalCount int;declare @totalPage int;declare @strCountSql nvarchar(1000); select top 1 @columnsName=columns.name from syscolumns as columns,sysobjects as objects where columns.id=objects.id and objects.name=@tableName;set @strCountSql='select @totalCount=count(1) from '+@tableName; exec sp_executesql @strCountSql,N'@totalCount int output',@totalCount OUTPUT;set @totalPage =@totalCount/@pageSize;if (@totalCount%@pageSize)<>0beginset @totalPage=@totalPage+1;endif @pageIndex>1 and @pageIndex<=@totalPagebeginset @strSql =' select top '+str(@pageSize) +' * from '+ @tableName+' where '+@columnsName+' > (select max('+@columnsName+') from '+@tableName+' where '+@columnsName+' in(select top ('+str(@pageSize*(@pageIndex-1))+') '+@columnsName+' from '+@tableName+' order by '+@columnsName+' asc)) order by '+@columnsName+' asc';endelse beginset @strSql =' select top '+str(@pageSize) +' * from '+ @tableName+' order by '+@columnsName+' asc'end exec sp_executesql @strSqlgoexec pagedQueryProc 1,20,'card' ---你只需要调用就可以了 动态分页 传入三个参数 1.分页当前页数 -- 2.一页显示记录数 --3.需查询的表名--是用了max函数进行分页