重写了MSSQL分页存储过程，附完整sql脚本

晚上花了1小时重写了MSSQL数据库中的分页存储过程，采用ROW_NUMBER的方式，需要MSSQL2005及以上版本支持。

为什么这么做，有两点：

第一：因为一直觉得采用TOP嵌套的方式太落后，尽管在程序中已经支持自定义View的方式读取分页，但是如果是单表的读取还是走存储过程，现在都MSSQL 2016了，MSSQL都支持ROW_NUMBER都10多年了，再不升级，别说客户和用户，自己都觉得太落后了。

第二：原来用吉日嘎拉的存储过程，有个bug，第1页的最后一行记录还会重复显示在第2页的第一条。修复bug也是硬指标。

完整MSSQL分页存储过程sql脚本如下，拿去不谢。

/****** Object:  StoredProcedure [dbo].[GetRecordByPage]    Script Date: 12/14/2017 22:44:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Troy Cui 崔文远
-- Create date: 2012年03月08日
-- Update date: 2017年12月14日
-- Description: 分页存储过程
-- =============================================
CREATE PROCEDURE [dbo].[GetRecordByPage] 
 @TableName          NVARCHAR(MAX),           -- 表名
    @SelectField        NVARCHAR(MAX) = '*',     -- 要显示的字段名(注意：不要加SELECT)
    @WhereConditional   NVARCHAR(MAX),           -- 查询条件(注意: 不要加WHERE)
    @SortExpression     NVARCHAR(MAX) = 'Id',    -- 排序索引字段名（注意：仅支持一个，多个时用Id DESC, Name格式）
    @PageSize           INT = 20,                -- 页大小
    @PageIndex          INT = 1,                 -- 页码
    @RecordCount        INT OUTPUT,              -- 返回记录总数
    @SortDire           NVARCHAR(MAX) = 'DESC'   -- 设置排序类型（注意：仅支持ASC或DESC）
AS
BEGIN
 DECLARE @CommandText NVARCHAR(MAX)      -- 主语句
 DECLARE @PageCount INT         -- 总共会是几页
 DECLARE @SQLRowCount NVARCHAR(MAX)     -- 用于查询记录总数的语句
 DECLARE @BeginRow INT     -- 开始记录
 DECLARE @EndRow INT      -- 结束记录
 DECLARE @TempLimit VARCHAR(MAX)   -- 结果范围
 SET @SortExpression = LTRIM(RTRIM(@SortExpression))
 SET @SortDire = UPPER(LTRIM(RTRIM(@SortDire)))
 
 --DECLARE @TimeDiff datetime
 --不返回计数（表示受 Transact-SQL 语句影响的行数）
 SET NOCOUNT ON
 --SELECT @TimeDiff=getdate() --记录时间
 
 -- 这里是计算整体记录行数
 IF @WhereConditional != ''
 BEGIN
   SET @SQLRowCount = 'SELECT @RecordCount=COUNT(*) FROM ' + @TableName + ' WHERE ' + @WhereConditional
 END
 ELSE
 BEGIN
   SET @SQLRowCount = 'SELECT @RecordCount=COUNT(*) FROM ' + @TableName
 END
 --输出参数为总记录数
 EXEC sp_executesql @SQLRowCount, N'@RecordCount INT OUT', @RecordCount OUT
 
 -- 这里是控制页数最多少
 SET @PageCount = @RecordCount / @PageSize + 1
 
 -- 这里检查当前页的有效性
 IF (@PageIndex < 1)
 BEGIN
  SET @PageIndex = 1
 END
 
 -- 这里限制最后一页的有效性
 IF (@PageIndex > @PageCount)
 BEGIN
  SET @PageIndex = @PageCount
 END
 
 SET @BeginRow = (@PageIndex - 1) * @pageSize + 1
 SET @EndRow = @PageIndex * @pageSize
 SET @TempLimit = 'ROWS BETWEEN ' + CAST(@BeginRow AS NVARCHAR) +' AND '+CAST(@EndRow AS NVARCHAR)
 --主查询返回结果集
 IF @PageIndex = 1
 BEGIN
  -- 第一页的显示效率提高
  IF @WhereConditional != ''
  BEGIN
   SET @CommandText = 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR) + ' ' +@SelectField+ ' FROM ' + @TableName + ' WHERE '+@WhereConditional+' ORDER BY '+@SortExpression+' '+@SortDire
  END
  ELSE
  BEGIN
   SET @CommandText = 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR) + ' ' +@SelectField+ ' FROM ' + @TableName + ' ORDER BY '+@SortExpression+' '+@SortDire
  END
 END
 ELSE
 BEGIN
  IF @WhereConditional != ''
  BEGIN
   SET @CommandText = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@SortExpression+' '+@SortDire+') AS ROWS ,'+@SelectField+' FROM '+@TableName+' WHERE '+@WhereConditional+') AS T WHERE '+@TempLimit 
  END
  ELSE
  BEGIN
   SET @CommandText = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@SortExpression+' '+@SortDire+') AS ROWS ,'+@SelectField+' FROM '+@TableName+') AS T WHERE '+@TempLimit 
  END
 END
 --PRINT @CommandText
 EXECUTE (@CommandText)
 --SELECT DATEDIFF(ms,@TimeDiff,getdate()) AS 耗时
 -- 这个是调试程序用的
 --SELECT @CommandText
 --INSERT INTO Temp_GetRecordByPage (CommandText) SELECT @CommandText 
 --返回计数
 SET NOCOUNT OFF
 --在存储过程的头部加上SET NOCOUNT ON 这样的话，在退出存储过程的时候加上 SET NOCOUNT OFF，以达到优化存储过程的目的。
END
GO