建立好如下的存储过程，以后要分页，直接调用改存储过程就可以了。

注意：数据量大、性能要求高的，请个性化处理。

复制代码 代码如下:

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