ex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList
+ ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '
+ @new_order2 + ' ) AS TMP ' + @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ ' SELECT TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList
+ ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
+ @new_order1 + ' ) AS TMP ' + @new_order1
END www.2cto.com
END
END
EXEC(@Sql)
--输出内容
SELECT @TotalCount as N'@TotalCount',
@TotalPageCount as N'@TotalPageCount'
2.一个比较创新的存储过程：(注：此存储过程利用了sql2005函数，适用于sql20005极其以上版本)
/****** 对象: StoredProcedure [dbo].[QueryPagination] 脚本日期: 05/14/2012 08:57:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------
* strFieldList 字段列表
* strTableList 查询表列表
* strWhereClause 查询条件
* strOrderFld 排序字段
* intTotoRecords (输入/输出)总记录数
* intTotoPages (输入/输出)总页数
* intPageSize 每页记录数
* intCurrentPage 当前页
* intCountToto 是否统计总数
* 0: 不容积总数
* 1: 统计总数
-------------------------------------------------*/
CREATE PROCEDURE [dbo].[QueryPagination]
@t char(1)='',
@strFieldList varchar(1000)='',
@strTableList varchar(300)='',
@strWhereClause varchar(1000)='',
@strOrderFld varchar(100)='',
@intCurrentPage int=1, www.2cto.com
@intPageSize int=10,
@intCountToto int=0,
@intTotoRecords int=0 out,
@intTotoPages int=0 out
AS
BEGIN
DECLARE @strSQL nvarchar(4000)
DECLARE @intCurPage int
DECLARE @strWhere varchar(200)
DECLARE @setvalue_error int
set @strWhere = ''; -- Where 语句
If @strWhereClause <> ''
set @strWhere = @strWhereClause;
Set xact_abort on
Begin Tran
SET @strSQL = 'SELECT COUNT(1) FROM ' + @strTableList + ' ' + @strWhere;
exec('DECLARE cur_t CURSOR FOR '+ @strSQL)
OPEN cur_t
FETCH NEXT FROM cur_t into @intTotoRecords
while @@fetch_status = 0
begin
fetch next from cur_t into @intTotoRecords
end www.2cto.com
close cur_t
deallocate cur_t
Set @setvalue_error = @@error
If @setvalue_error<>0
Begin
Set @intTotoRecords = -1;
GOTO DoNext;
Rollback Tran
End
Else
Begin
Commit Tran
GOTO DoNext;
End
DoNext:
IF @intTotoRecords<0
GOTO errTotoRecords; -- 返回错误：记录总数错误
IF @intPageSize<=0
GOTO errPageSize; -- 返回错误：每页记录数范围错误
-- 计算出总页数
IF @intTotoRecords%@intPageSize <> 0
set @intTotoPages = cast(@intTotoRecords/@intPageSize as int) + 1;
ELSE www.2cto.com
set @intTotoPages=cast(@intTotoRecords/@intPageSize as int) ;
-- 确定待查询的页码数
-- 如果页码数小于等于 0 ，则查询返回第一页
-- 如果页码数大于最大页码数，则查询返回最后一页
IF @intCurrentPage<=0
set @intCur