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

 

 

http://www.cnblogs.com/livexy/archive/2010/07/06/1771936.html