通用分页存储过程高效动态表

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函数进行分页