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

原作者: [db:作者] 来自: [db:来源] 收藏邀请

建立好如下的存储过程，以后要分页，直接调用改存储过程就可以了。
注意：数据量大、性能要求高的，请个性化处理。

 
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 