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个回答

你的第二个游标结束时的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]

liuyinhuan0409
liuyinhuan0409 哦,呵呵,那就好~
接近 8 年之前 回复
zfu3382548
zfu3382548 是的,太不小心了 后面改回来了
接近 8 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问