高性能SQLServer通用分页存储过程

这是我之前整理的高性能SQLServer 通用分页存储过程，测试性能还不错，特此分享出来，如果有人能更好地优化，请留言，谢谢！

USE [数据库名称]
GO
/****** Object:  StoredProcedure [dbo].[dbTable_PagerHelper]    Script Date: 08/22/2010 13:30:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  张红君(Jarry)
-- Create date: 2009-08-01
-- Description: 通用分页存储过程
-- =============================================
ALTER PROCEDURE [dbo].[dbTable_PagerHelper]
 @TableName VARCHAR(50), --表名
 @FieldNames VARCHAR(1000), --显示列名，如果是全部字段则为*
 @WhereString VARCHAR(256) = NULL, --查询条件 不含'WHERE'字符，如[id]>5 AND [userid]>10000
 @OrderField VARCHAR(256) = NULL, --排序不含'ORDER BY'字符，当@SortType=3时生效，必须指定ASC或DESC，建议在最后加上主键
 @OrderType TINYINT, --排序规则（1:单列正序ASC；2:单列倒序DESC；3:多列排序；）
 @PageIndex INT, --当前页数
 @PageSize INT, --每页输出的记录数
 @RecorderCount INT = 0 --记录总数，如果小于等于0则重新统计总数
AS
BEGIN
 SET NOCOUNT ON;
    DECLARE @LowerBound int, @UpperBound int;
    SET @LowerBound = (@PageSize * (@PageIndex - 1));
    SET @UpperBound = (@LowerBound + @PageSize - 1);
    DECLARE @MSSQL NVARCHAR(3000), @Where NVARCHAR(500), @Order VARCHAR(256), @Order2 VARCHAR(256);
    SET @MSSQL = '';
    SET @Where = '';
    SET @Order = '';
    SET @Order2 = '';
    IF((@WhereString IS NOT NULL) AND (@WhereString != ''))
  SET @Where = ' WHERE ' + @WhereString;
 IF((@OrderType IS NOT NULL) AND (@OrderType > 0))
 BEGIN
  SET @Order = ' ORDER BY ' + @OrderField;
  IF(@OrderType = 1)
   SET @Order = @Order + ' ASC';
  ELSE IF(@OrderType = 2)
   SET @Order = @Order + ' DESC';
  SET @Order2 = REPLACE(REPLACE(UPPER(@Order), ' ASC', ' {ASC}'), ' DESC', ' {DESC}');
  SET @Order2 = REPLACE(REPLACE(UPPER(@Order2), ' {ASC}', ' DESC'), ' {DESC}', ' ASC');
 END
 
 --重新统计总记录数
    IF(@RecorderCount <= 0)
  EXECUTE('SELECT COUNT(*) AS [RecorderCount] FROM ' + @TableName + @Where);
    ELSE
  SELECT @RecorderCount AS [RecorderCount];
 
 IF(@PageIndex <= 1) --如果是第一页
 BEGIN
  SET @MSSQL = 'SELECT TOP ' + STR(@PageSize) +' '+ @FieldNames + ' FROM ' + @TableName + @Where + @Order;
 END
 ELSE IF((@PageSize*@PageIndex) >= @RecorderCount) --如果是最后一页
 BEGIN
  SET @MSSQL = 'SELECT ' + @FieldNames + ' FROM (SELECT TOP ' + STR(@PageSize - ((@PageSize*@PageIndex) - @RecorderCount)) + ' ' + @FieldNames + ' FROM ' + @TableName + @Where + @Order2 + ') AS [PagerTempTable]' + @Order;
 END
 ELSE
    BEGIN
  IF(@OrderType < 3) --单列排序分页方法
  BEGIN
   SET @MSSQL = 'SELECT TOP ' + STR(@PageSize);
   SET @MSSQL = @MSSQL + ' ' + @FieldNames;
   SET @MSSQL = @MSSQL + ' FROM ' + @TableName;
   SET @MSSQL = @MSSQL + @Where;
   DECLARE @TempStrings NVARCHAR(500);
   SET @TempStrings = '';
   IF(@OrderType > 0)
   BEGIN
    IF(@Where <> '')
     SET @TempStrings = @TempStrings + ' AND';
    ELSE
     SET @TempStrings = ' WHERE ';
    SET @TempStrings = @TempStrings + ' ' + @OrderField;
    
    IF(@OrderType = 1)
    BEGIN
     SET @TempStrings = @TempStrings + ' > (SELECT MAX';
    END
    ELSE
    BEGIN
     SET @TempStrings = @TempStrings + ' < (SELECT MIN';
    END
    SET @TempStrings = @TempStrings + '(' + @OrderField + ') FROM (SELECT TOP '+STR(@LowerBound)+' ' + @OrderField + ' FROM ' + @TableName + @Where + @Order;
    SET @TempStrings = @TempStrings + ') AS [PagerTempTable])';
    
   END
   SET @MSSQL = @MSSQL + @TempStrings + @Order;
  END
  ELSE --多列排序分页方法
  BEGIN
   SET @MSSQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldNames + ' FROM (SELECT TOP ' + STR(@PageSize) + ' ' + @FieldNames + ' FROM (SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldNames + ' FROM ' + @TableName + @Where + @Order + ') AS [PagerTempTable]' + @Order2 + ') AS [PagerTempTable]' + @Order + ';'
  END
    END
    EXECUTE(@MSSQL);
END