|
CREATE PROCEDURE PrSqlPager
( @TableName nvarchar(100), -- 表名 @StrWhere nvarchar(500), -- Where 子句 @IdentityKey nvarchar(100), -- 标识列 @PageNum int, -- 页号 @PageSize int, -- 页大小 @ErrInfo varchar(100) OUTPUT, -- 出错信息
@TotalRecordCount int OUTPUT, -- 总记录数 @PageCount int OUTPUT -- 总页数 ) AS DECLARE @sql NVARCHAR(1000)
DECLARE @paramDefine NVARCHAR(1000) /******************** 数据表中总记录数 ***********************/
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 /*************************************************************/ /******************** 根据页大小计算总页数 ***********************/
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 /*************************************************************/ /******************** 实现分页查询 ***********************/
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 |
|
|
