CREATE   PROCEDURE PrSqlPager
(
        @TableName  nvarchar(100),    -- 表名
        @StrWhere nvarchar(500),    -- Where 子句
        @IdentityKey  nvarchar(100),   -- 標(biāo)識列
        @PageNum   int,   -- 頁號
        @PageSize   int,   -- 頁大小

        @ErrInfo varchar(100)  OUTPUT,  -- 出錯信息
        @TotalRecordCount int OUTPUT,   -- 總記錄數(shù)
        @PageCount int OUTPUT   -- 總頁數(shù)
)
AS

DECLARE @sql NVARCHAR(1000)
DECLARE @paramDefine NVARCHAR(1000)

/******************** 數(shù)據(jù)表中總記錄數(shù) ***********************/
SET @sql = ‘SELECT @TotalRecordCount = COUNT(*) FROM ‘ + @TableName + ‘ WHERE 1=1‘
IF @StrWhere IS NOT NULL AND @StrWhere <> ‘‘
 SET @sql = @sql + ‘ AND ‘ + @StrWhere

SET @paramDefine = ‘@TotalRecordCount INT OUTPUT‘
EXEC SP_EXECUTESQL @sql,@paramDefine,@TotalRecordCount OUTPUT
/*************************************************************/

/*********************** 驗證頁大小 **************************/
IF @PageSize < 0
 SET @PageSize = 0
IF @PageSize > @TotalRecordCount
 SET @PageNum = @TotalRecordCount
/*************************************************************/

/******************** 根據(jù)頁大小計算總頁數(shù) ***********************/
IF @TotalRecordCount % @PageSize = 0
 SET @PageCount = @TotalRecordCount / @PageSize
ELSE
 SET @PageCount = @TotalRecordCount / @PageSize + 1
/*************************************************************/

/*********************** 驗證頁號 ***************************/
IF @PageNum < 1
 SET @PageNum = 1
IF @PageNum > @PageCount
 SET @PageNum = @PageCount
/*************************************************************/

/******************** 實現(xiàn)分頁查詢 ***********************/
DECLARE @foreRecordCount INT
SET @foreRecordCount = (@PageNum - 1) * @PageSize + 1
SET @sql = ‘SELECT TOP ‘ + CAST(@PageSize as varchar)
 + ‘ * FROM ‘ + @TableName
 + ‘ WHERE ‘ + @IdentityKey + ‘ >= ‘
 + ‘ ( SELECT MAX( ‘ + @IdentityKey + ‘ ) FROM ( SELECT TOP ‘
 + CAST(@foreRecordCount as varchar)
 + @IdentityKey + ‘ FROM ‘ + @TableName
 + ‘ ORDER BY ‘ + @IdentityKey + ‘ ) AS TempTable ) ‘
 + ‘ ORDER BY ‘ + @IdentityKey

IF @StrWhere IS NOT NULL AND @StrWhere <> ‘‘
 SET @sql = @sql + ‘ AND ‘ + @StrWhere

SET @paramDefine = N‘@PageSize INT,@foreRecordCount INT‘
EXEC SP_EXECUTESQL @sql,@paramDefine,@PageSize,@foreRecordCount
/*************************************************************/

GO