一个基于ROW_NUMBER()的通用分页存储过程代码

ALTER PROCEDURE [dbo].[COMMON_PROCEDURE_SelectWithPage]
@Sql VARCHAR(5000),
@CurrentPageNo INT,
@PageSize INT,
@TotalNum INT OUTPUT
AS
SET NOCOUNT ON
DECLARE @SqlCmd VARCHAR(5000)
------------------------------------------ --查询数据
SET @SqlCmd = SELECT * FROM ( + @Sql + ) A WHERE RowIndex BETWEEN + CONVERT(VARCHAR,(@CurrentPageNo-1) * @PageSize + 1) + AND + CONVERT(VARCHAR,@CurrentPageNo * @PageSize)
EXEC(@SqlCmd) PRINT (@SqlCmd)
------------------------------------------ --求记录总数
IF @TotalNum = -1
BEGIN
CREATE TABLE #Temp1(num INT)
INSERT INTO #Temp1
EXEC(SELECT count(*) FROM ( + @Sql + ) A)
SELECT @TotalNum=(SELECT * FROM #Temp1)
DROP TABLE #Temp1
END

用法很简单，但必须在传入的SQL中使用ROW_NUMBER() OVER(...) AS RowIndex ：
DECLARE @Sql VARCHAR(5000)
DECLARE @CurrentPageNo INT
DECLARE @PageSize INT
DECLARE @TotalNum INT

SET @CurrentPageNo = 100
SET @PageSize = 10
SET @TotalNum = -1
SET @Sql = SELECT *, ROW_NUMBER() OVER (ORDER BY 排序字段) AS RowIndex FROM 表名 A WITH (NOLOCK)

EXEC [dbo].[COMMON_PROCEDURE_SelectWithPage] @Sql,@CurrentPageNo,@PageSize,@TotalNum OUTPUT

SELECT @TotalNum