一个基于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