愁死我了，存储过程执行的结果不稳定。

lqhly 2003-12-18 12:18:41

我有一存储过程，在SQL SERVER 上为：
//=====
CREATE PROCEDURE pr_ms_sale
@lc_jyh char(12),
@ret_val int out
AS
DECLARE @bmh char(6)
DECLARE @rq datetime
DECLARE @sj datetime
DECLARE @yyyh char(6)
DECLARE @spbh char(18)
DECLARE @sl int
DECLARE @sjje numeric(10,2)
DECLARE @zke numeric(10,2)
DECLARE @ls_jyh char(10)
DECLARE @year char(4)
DECLARE @month char(2)
DECLARE @skyh char(6)

DECLARE csr_lsd cursor for
select bmh,rq,sj,yyyh,spbh,sl,sjje,zke,skyh from lslsz where jyh = @lc_jyh
begin transaction
open csr_lsd
fetch next from csr_lsd into @bmh,@rq,@sj,@yyyh,@spbh,@sl,@sjje,@zke,@skyh
while (@@fetch_status = 0 )
begin
/********* dpxstz ****************** begin */
if not exists(select 1 from dpxstz where rq = @rq and spbh = @spbh)
insert into dpxstz(rq,bmh,spbh,xssl,cxsje,xszk)
values(@rq,@bmh,@spbh,@sl,@sjje,@zke)
else
update dpxstz set xssl = xssl + @sl,cxsje = cxsje + @sjje,xszk = xszk + @zke
where rq = @rq and spbh = @spbh
if @@error<>0
begin
rollback transaction
close csr_lsd
deallocate csr_lsd
select @ret_val=-1
return
end
/********* dpxstz ****************** end */

/********* pfxstz ****************** begin */
if not exists(select 1 from pfxstz where pzh = @lc_jyh)
insert into pfxstz(rq,sj,bmh,pzh,pfsbh,sl,yyyh,sjje,zke)
values(@rq,@sj,@bmh,@lc_jyh,@skyh,@sl,@yyyh,@sjje,@zke)
else
update pfxstz set sl = sl + @sl,sjje = sjje + @sjje,zke = zke + @zke
where pzh = @lc_jyh
if @@error<>0
begin
rollback transaction
close csr_lsd
deallocate csr_lsd
select @ret_val=-1
return
end
/********* pfxstz ****************** end */

/********* yyyxstz ****************** begin */
if not exists(select 1 from yyyxstz where rq = @rq and yyyh = @yyyh)
insert into yyyxstz(rq,yyyh,jycs,xssl,cxsje,xszk)
values(@rq,@yyyh,1,@sl,@sjje,@zke)
else
update yyyxstz set jycs = jycs + 1,xssl = xssl + @sl,cxsje = cxsje + @sjje,xszk = xszk + @zke
where rq = @rq and yyyh = @yyyh
if @@error<>0
begin
rollback transaction
close csr_lsd
deallocate csr_lsd
select @ret_val=-1
return
end
/********* yyyxstz ****************** end */
/********* kcz ****************** begin */
execute pr_rqgetny @bmh,@rq,@year output,@month output
if not exists(select 1 from kcz where year=@year and month=@month and bmh=@bmh and spbh=@spbh)
insert into kcz(year,month,bmh,spbh,dqsl) values(@year,@month,@bmh,@spbh,0 - @sl)
else
update kcz set dqsl=dqsl-@sl
where year=@year and month=@month and bmh=@bmh and spbh=@spbh
if @@error<>0
begin
rollback transaction
close csr_lsd
deallocate csr_lsd
select @ret_val=-1
return
end
/********* kcz ****************** end */
fetch next from csr_lsd into @bmh,@rq,@sj,@yyyh,@spbh,@sl,@sjje,@zke,@skyh
end

commit transaction
close csr_lsd
deallocate csr_lsd
select @ret_val=100

go
//====

pb8.0上的调用为：
//====
long ll_success
ll_success = 100
declare sp_ms_sale procedure for pr_ms_sale
@lc_jyh = :ls_jyh,@ret_val = :ll_success output
using sqlca;
execute sp_ms_sale;
fetch sp_ms_sale into :ll_success;
if sqlca.sqlcode <> 0 then
MessageBox("sqlerror_pr_ms_sale ===== ",sqlca.sqlerrtext)
ll_success = -1
end if
if ll_success = -1 then
messagebox('错误','数据保存失败'+sqlca.sqlerrtext,stopsign!)
delete lslsz where jyh = :ls_jyh using sqlca;
close sp_ms_sale;
return
end if
close sp_ms_sale;
//======
经常会出现存储过程仅行到select bmh,rq,sj,yyyh,spbh,sl,sjje,zke,skyh from lslsz where jyh = @lc_jyh 后就退出的情况。
例如：@lc_jyh为销售的交易号
参数为：@lc_jyh =‘03121700001’,@ret_val = 100 时
存储过程的执行结果是正确的，用跟踪结果查看为：
//===
1）select object_id('pr_ms_sale')
2）select type, length, prec, scale from syscolumns
where id = 727673640 and name = '@ret_val'
3）DECLARE @P002 int SELECT @P002 = 100
execute pr_ms_sale @lc_jyh = '03121700001',
@ret_val = @P002 output SELECT @P002
4）SET NOEXEC OFF
5） SELECT lslsz.bmh,
lslsz.rq,
lslsz.sj,
lslsz.jyh,
lslsz.yyyh,
lslsz.skyh,
lslsz.spbh,
lslsz.sl,
lslsz.lsj,
lslsz.sjje,
lslsz.zke,
lslsz.spmc
FROM lslsz
WHERE jyh = '03121700001'
6）SELECT N'Testing Connection...'
7）EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
8）SELECT N'Testing Connection...'
//===
而参数为：@lc_jyh =‘03121700006’,@ret_val = 100 时
1）-- 7）同上，而8）为execute dbo.sp_td;1 为什么？

...全文