游标问题，在线等。。

[hzyzx]

我有下面一段代码，程序运行结果正确，但是老是在死循环，希望大家看看，谢谢

CREATE PROCEDURE [dbo].[i]
AS

if exists (select gp_cz,gp_dbpf,gp_zdz,gp_zwf,count(gp_zwf) as zong
from newdz where gp_zwf >'**'
group by gp_cz,gp_dbpf,gp_zdz,gp_zwf
having count(gp_zwf)=2)
begin

declare @dz char(20)
declare @wf varchar(2)
declare @cz char(30)
declare @dbpf char(40)
declare @XZXJ char(16)
declare @xzxj1 char(16)
declare @zong int
declare @bh varchar(8)
declare dd cursor for SELECT gp_cz,gp_dbpf,gp_zdz,gp_zwf,count(gp_zwf) as zong
from newdz where gp_zwf >'**'
group by gp_cz,gp_dbpf,gp_zdz,gp_zwf
having count(gp_zwf)=2

open dd
fetch next from dd into @cz,@dbpf,@dz,@wf,@zong
while @@fetch_status=0
begin
declare cc cursor for
select xzxj from newdz where gp_cz=@cz and gp_dbpf=@dbpf and gp_zwf=@wf
open cc
fetch next from cc into @xzxj
while @@fetch_status=0
fetch next from cc into @xzxj1
while @@fetch_status=0
close cc
deallocate cc

declare ee cursor for
select yzk.bh from yzk where yzk.teshu ='双线压着' and yzk.dzmc=@dz and
((yzk.xzxj=@xzxj and yzk.xzxj1=@xzxj1) or (yzk.xzxj=@xzxj1 and yzk.xzxj1=@xzxj))
open ee
fetch next from ee into @bh
while @@fetch_status=0
begin
update newdz set bz=@bh where gp_zwf=@wf and gp_cz=@cz and gp_dbpf=@dbpf -- where current of ee
end
close ee
deallocate ee
end
fetch next from dd into @cz,@dbpf,@dz,@wf,@zong
end
close dd
deallocate dd
GO

[zjcxc]

CREATE PROCEDURE [dbo].[i]
AS

if exists (select gp_cz,gp_dbpf,gp_zdz,gp_zwf,count(gp_zwf) as zong
from newdz where gp_zwf >'**'
group by gp_cz,gp_dbpf,gp_zdz,gp_zwf
having count(gp_zwf)=2)
begin

declare @dz char(20)
declare @wf varchar(2)
declare @cz char(30)
declare @dbpf char(40)
declare @XZXJ char(16)
declare @xzxj1 char(16)
declare @zong int
declare @bh varchar(8)
declare dd cursor for SELECT gp_cz,gp_dbpf,gp_zdz,gp_zwf,count(gp_zwf) as zong
from newdz where gp_zwf >'**'
group by gp_cz,gp_dbpf,gp_zdz,gp_zwf
having count(gp_zwf)=2

open dd
fetch next from dd into @cz,@dbpf,@dz,@wf,@zong
while @@fetch_status=0
begin
declare cc cursor for
select xzxj from newdz where gp_cz=@cz and gp_dbpf=@dbpf and gp_zwf=@wf
open cc
fetch next from cc into @xzxj
while @@fetch_status=0
fetch next from cc into @xzxj1
-- while @@fetch_status=0 --这个你想干什么?
close cc
deallocate cc

declare ee cursor for
select yzk.bh from yzk where yzk.teshu ='双线压着' and yzk.dzmc=@dz and
((yzk.xzxj=@xzxj and yzk.xzxj1=@xzxj1) or (yzk.xzxj=@xzxj1 and yzk.xzxj1=@xzxj))
open ee
fetch next from ee into @bh
while @@fetch_status=0
begin
update newdz set bz=@bh where gp_zwf=@wf and gp_cz=@cz and gp_dbpf=@dbpf -- where current of ee
end
close ee
deallocate ee
end
fetch next from dd into @cz,@dbpf,@dz,@wf,@zong
end
close dd
deallocate dd
GO

[pbsql]

...
while @@fetch_status=0
begin
declare cc cursor for
select xzxj from newdz where gp_cz=@cz and gp_dbpf=@dbpf and gp_zwf=@wf
open cc
fetch next from cc into @xzxj
while @@fetch_status=0
fetch next from cc into @xzxj1--**********这三句什么意思？？？？？
while @@fetch_status=0 --**********这句死循环了
close cc
deallocate cc

declare ee cursor for
select yzk.bh from yzk where yzk.teshu ='双线压着' and yzk.dzmc=@dz and
((yzk.xzxj=@xzxj and yzk.xzxj1=@xzxj1) or (yzk.xzxj=@xzxj1 and yzk.xzxj1=@xzxj))
open ee
fetch next from ee into @bh
while @@fetch_status=0
begin
update newdz set bz=@bh where gp_zwf=@wf and gp_cz=@cz and gp_dbpf=@dbpf -- where current of ee
fetch next from ee into @bh--***********加了这一句
end
close ee
deallocate ee
end
fetch next from dd into @cz,@dbpf,@dz,@wf,@zong
end
close dd
deallocate dd
GO

[liuxf0201]

while @@fetch_status=0
fetch next from cc into @xzxj1
while @@fetch_status=0
close cc
deallocate cc
将close cc前的while @@fetch_status=0 语句去掉

[pbsql]

完整的如下：
CREATE PROCEDURE [dbo].[i]
AS

if exists (select gp_cz,gp_dbpf,gp_zdz,gp_zwf,count(gp_zwf) as zong
from newdz where gp_zwf >'**'
group by gp_cz,gp_dbpf,gp_zdz,gp_zwf
having count(gp_zwf)=2)
begin

declare @dz char(20)
declare @wf varchar(2)
declare @cz char(30)
declare @dbpf char(40)
declare @XZXJ char(16)
declare @xzxj1 char(16)
declare @zong int
declare @bh varchar(8)
declare dd cursor for SELECT gp_cz,gp_dbpf,gp_zdz,gp_zwf,count(gp_zwf) as zong
from newdz where gp_zwf >'**'
group by gp_cz,gp_dbpf,gp_zdz,gp_zwf
having count(gp_zwf)=2

open dd
fetch next from dd into @cz,@dbpf,@dz,@wf,@zong

while @@fetch_status=0
begin
declare cc cursor for
select xzxj from newdz where gp_cz=@cz and gp_dbpf=@dbpf and gp_zwf=@wf
open cc
fetch next from cc into @xzxj
if @@fetch_status=0--********这里不要循环************
fetch next from cc into @xzxj1--************取第二个值*********
if @@fetch_status<>0 --**********这里不要循环，取不到值就退出
begin
close cc
deallocate cc
continue
end
close cc
deallocate cc
declare ee cursor for
select yzk.bh from yzk where yzk.teshu ='双线压着' and yzk.dzmc=@dz and
((yzk.xzxj=@xzxj and yzk.xzxj1=@xzxj1) or (yzk.xzxj=@xzxj1 and yzk.xzxj1=@xzxj))
open ee
fetch next from ee into @bh
while @@fetch_status=0
begin
update newdz set bz=@bh where gp_zwf=@wf and gp_cz=@cz and gp_dbpf=@dbpf -- where current of ee
fetch next from ee into @bh--***********加了这一句以免死循环********
end
close ee
deallocate ee
end
fetch next from dd into @cz,@dbpf,@dz,@wf,@zong
end
close dd
deallocate dd
GO

[hzyzx]

fetch next from cc into @xzxj
while @@fetch_status=0
fetch next from cc into @xzxj1--**********这三句什么意思？？？？？
while @@fetch_status=0
这里是取出前两个符合条件数据

[liuxf0201]

要取前两个符合条件的数据，
可以用declare cc cursor for
select top 2 xzxj from newdz where gp_cz=@cz and gp_dbpf=@dbpf and gp_zwf=@wf

[hzyzx]

哈哈，问题解决了，高手就是高手