sql分页存储过程简析(6)
来源：未知 责任编辑：责任编辑 发表时间:2015-03-01 01:38 点击:次
@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 @intCurPage=1;
ELSE IF @intCurrentPage>@intTotoPages
set @intCurPage = @intTotoPages;
ELSE
set @intCurPage=@intCurrentPage;
If @strWhere <> ''
Set @strSQL = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' + @strOrderFld + ') ROWNUM,' + @strFieldList + ' FROM ' + @strTableList + ' ' + @strWhere +
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 更多>>
