MSSQL分页：查询前几页速度最快的ROW_NUMBER() OVER分页存贮过程

代码

CREATE PROCEDURE [ dbo ] . [ getPagerROWOVER ]
@PageIndex int = 1 ,
@PageSize int = 10 ,
@Tables nvarchar ( 1000 ) = NULL ,
@Fields nvarchar ( 2000 ) = ' * ' ,
@Where nvarchar ( 2000 ) = NULL ,
@GroupBy nvarchar ( 2000 ) = NULL ,
@OrderBy nvarchar ( 1000 ) = NULL ,
@ReturnCount int = 0 OUTPUT
AS
BEGIN
SET NOCOUNT ON ;
declare @Sql nvarchar ( 4000 ), @startRowIndex int , @maximumRows int ;
declare @strWhere nvarchar ( 2000 )
declare @strGroupBy nvarchar ( 2000 )

set @startRowIndex = ( @PageIndex - 1 ) * @PageSize ;
set @maximumRows = @PageSize ;

IF @Where IS NOT NULL AND @Where != ''
BEGIN
SET @strWhere = ' WHERE ' + @Where
END
ELSE
BEGIN
SET @strWhere = ''
END
IF @GroupBy IS NOT NULL AND @GroupBy != ''
BEGIN
SET @strGroupBy = ' GROUP BY ' + @GroupBy
END
ELSE
BEGIN
SET @strGroupBy = ''
END

set @Sql = ' select ' + @Fields + ' from (select ' + @Fields + ' , ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ' ) as rownum from ' + @Tables + @strWhere + @strGroupBy + ' ) as tmpTable where rownum > ' + CONVERT ( nvarchar ( 10 ), @startRowIndex ) + ' and rownum <= ( ' + CONVERT ( nvarchar ( 10 ), @startRowIndex ) + ' + ' + CONVERT ( nvarchar ( 10 ), @maximumRows ) + ' ) '
exec ( @Sql )
print @sql
set @Sql = ' select @ReturnCount=COUNT(*) from ' + @Tables + @strWhere
exec sp_executesql @Sql ,N ' @ReturnCount int OUTPUT ' , @ReturnCount OUTPUT
print @sql
END

 

转载于:https://www.cnblogs.com/livexy/archive/2010/07/06/1771936.html