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

 

posted @ 2010-07-06 10:44  熊哥  阅读(1110)  评论(0编辑  收藏  举报