zfu3382548
2012-08-30 09:43
浏览 488

sqlserver 存储过程,游标嵌套死循环,求指导。

[code="sql"]if(OBJECT_ID('pro_fristCount','P') is not null)

drop procedure pro_fristCount;

go
create procedure pro_fristCount
as
create table #depe_records( -- 创建临时表
deptId nvarchar(100),
deptName nvarchar(100),
itemId nvarchar(100),
itemName nvarchar(100),
ticketNum int,
voteId nvarchar(100)
);
declare @temp_deptId nvarchar(100),@temp_deptName nvarchar(100),@temp_itemId nvarchar(100),@temp_itemName nvarchar(100),@temp_ticketNum int,@temp_voteId nvarchar(100);--临时表数据
declare @deptId nvarchar(100);
declare cur_group cursor for select deptId from sp_vote_dept_records ;--按照部门分组
open cur_group
fetch next from cur_group into @deptId;
while(@@FETCH_STATUS=0)

begin

print @deptId;
declare cur_votedept cursor for select top 10 * from sp_vote_dept_records
where deptId=@deptId order by deptId desc ;--每个部门的十件大事
open cur_votedept

fetch next from cur_votedept into @temp_deptId,@temp_deptName,@temp_itemId,@temp_itemName,@temp_ticketNum,@temp_voteId;
while(@@FETCH_STATUS=0)
begin
print @temp_deptId+@temp_deptName+@temp_itemId;
insert into #depe_records values(@temp_deptId,@temp_deptName,@temp_itemId,@temp_itemName,@temp_ticketNum,@temp_voteId);
end
fetch next from c_showuser into @temp_deptId,@temp_deptName,@temp_itemId,@temp_ticketNum,@temp_voteId;
close c_showuser;

deallocate cur_votedept

fetch next from cur_group into @deptId;

end

close cur_group
deallocate cur_group
return select * from #depe_records; [/code]

第二个游标只会遍历不大于十条是数据,但是却总是死循环。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • liuyinhuan0409 2012-08-30 10:13
    已采纳

    你的第二个游标结束时的c_showuser哪来的?是不是写错了?
    这个:
    fetch next from c_showuser into @temp_deptId,@temp_deptName,@temp_itemId,@temp_ticketNum,@temp_voteId;
    还有你的结束时的fetch是不是要写在begin end里面?
    这段是不是要改成这样:
    [code="sql"]
    open cur_votedept

    fetch next from cur_votedept into @temp_deptId,@temp_deptName,@temp_itemId,@temp_itemName,@temp_ticketNum,@temp_voteId;
    while(@@FETCH_STATUS=0)
    begin
    print @temp_deptId+@temp_deptName+@temp_itemId;
    insert into #depe_records values(@temp_deptId,@temp_deptName,@temp_itemId,@temp_itemName,@temp_ticketNum,@temp_voteId);
    fetch next from cur_votedept into @temp_deptId,@temp_deptName,@temp_itemId,@temp_ticketNum,@temp_voteId;
    end
    close cur_votedept;

    deallocate cur_votedept

    [/code]

    点赞 评论

相关推荐 更多相似问题