ALTER procedure Consignment @tablename varchar(80) , @strOrder varchar(50) , @PageIndex int = 1, @PageSize int = 15, @strGetFields varchar(200) = '*', @OutPut int output as Begin Declare @strSql varchar(500) DECLARE @SQL NVARCHAR(1000) DECLARE @R BIGINT SET @SQL= N'select @R=count(*) from '+@TableName EXEC SP_EXECUTESQL @SQL, N' @R BIGINT OUTPUT', @R OUTPUT SET @OutPut= @R if(@PageIndex =1) Begin set @strSql='select top '+str(@PageSize)+' '+@strGetFields+' from '+@tablename+' order by '+@strOrder End Else set @strSql='select top '+str(@PageSize)+' '+@strGetFields+' from '+@tablename+' where ('+@strOrder +' >= ( select Max('+@strOrder+') from ( select top '+str(@PageSize*@PageIndex)+' * from '+@tablename+' order by ' +@strOrder+' ) as tempTable)) order by '+ @strOrder select @strSql exec(@strSql) End alter procedure AllProce @tablename varchar(200) , --表名 @strGetFields varchar(200) = '*', --查询列名 @PageIndex int = 1 , --页码 @pageSize int = 15, --页面大小 @strWhere varchar(100) = '', --查询条件 @strOrder varchar(100) = '', --排序列名 @intOrder bit = 0, --排序类型 1为升序 @CountAll bigint output --返回纪录总数用于计算页面数 as begin declare @strSql varchar(500) --主语句 declare @strTemp varchar(100) --临时变量 declare @strOrders varchar(50) --排序语句 declare @table varchar(70) declare @SQL nvarchar(1000) declare @R bigint set @SQL= N'select @R=count(*) from '+convert(nvarchar(200),@TableName) exec SP_EXECUTESQL @SQL, N' @R BIGINT OUTPUT', @R OUTPUT set @CountAll= @R if @intOrder = 0 begin --为0是升序 set @strTemp = '>(select max' set @strOrders = ' order by '+@strOrder+' asc ' end else begin --否则为降序 set @strTemp = '<(select min' set @strOrders = ' order by '+@strOrder+' desc ' end if @PageIndex =1 --第一页直接读出纪录 begin if @strWhere = '' begin set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' '+@strOrders end else begin set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+ ' from '+@tablename+' where '+@strWhere+' '+@strOrders end end else begin set @strSql = 'select top'+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strOrder+' '+@strTemp+' ('+@strOrder+')' +' from (select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+ ' '+@strOrders+ ') as tempTable ) '+@strOrders if @strWhere != ' ' begin set @strSql = 'select top '+str(@pageSize)+ ' '+@strGetFields+' from '+@tablename+ ' where '+@strOrder+ ' '+@strTemp+' ('+@strOrder+') ' +' from(select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strWhere+' ' +@strOrders+') as tempTable) where '+@strWhere+' '+@strOrders end end exec(@strSql) end GO 相关资源：SqlServer高效万能分页存储过程
