[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]
第二个游标只会遍历不大于十条是数据，但是却总是死循环。
你的第二个游标结束时的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]