急,informix存储过程报错!
我用informix数据库执行ST_ycheckSheet存储过程,如下:
execute procedure ST_ycheckSheet('H001200902180186','ss');
报错为 语句(1)运行失败:General SQL error.
Variable (__startwork) has undefined value.
请高手指点一下怎么修改,谢谢!~~
drop procedure ST_ycheckSheet;
create procedure ST_ycheckSheet(sSheetID char(16),sUserCName char(8)) returning Int;
define __Err,__BreakPoint,__ISAM int;
define __Msg varchar(255);
define __StartWork Int;
define __dutytypeInt;
define __flag,__tflag,__c,__iInt;
define __mode Int;
define __myshopid,__zbshopid,__headid char(4);
define __shoptypeInt;
define __SheetType int;
ON EXCEPTION SET __Err,__ISAM,__Msg
if __StartWork = 1 then
rollback work;
end if;
raise exception -746,0,__Err||','||__ISAM||','||__BreakPoint||'-'||__Msg;
END EXCEPTION
let __BreakPoint = 814600;
let __SheetType = 8146;
begin work;
let __StartWork = 1;
let __BreakPoint = 814605;
select shopid into __MyShopID from ychecksheet0 where sheetid=sSheetID;
select shoptype,headid into __shoptype,__headid from shop where id=__myshopid;
select id into __zbshopid from shop where id=headid and ifshopgroup=(select ifshopgroup from shop where id=__myshopid);
let __breakpoint=814606;
select flag,dutytype into __flag,__dutytype from ycheckSheet0 where sheetid=sSheetID;
select count(*) into __c from ychecksheetItem0 a,employee b where a.id=b.id and a.sheetid=sSheetID and b.dutyid in(1009,1010,1013,1014,1137);
if __flag=0 and __dutytype=0 and __c>0 then
raise exception -746,0,'选择员工类时不能有管理人员!';
end if;
if __flag=0 and __dutytype=0 then
let __tflag=100;
elif __flag=1 and __dutytype in(1,2) then
let __tflag=100;
elif __flag=2 then
let __tflag=100;
else
let __tflag=__flag+1;
end if;
if __shoptype=0 and __tflag<>100 then
let __tflag=__flag+2;
end if;
let __BreakPoint = 814608;
update ycheckSheet0 set (Flag,CheckDate,Checker,PrintCount)=(__tflag,ToDay,sUserCName,0) where SheetID=sSheetID;
if dbinfo('sqlca.sqlerrd2')=0 then
raise exception -746,0,sSheetID || '单并不存在!';
end if;
let __BreakPoint = 814610;
insert into SheetFlow0(SheetType,SheetID,BeforeFlag,AfterFlag,UserName,Note)
values(__SheetType,sSheetID,__flag,__tflag,sUserCName,'审核');
let __BreakPoint = 814615;
insert into SheetFlow select * from SheetFlow0 where SheetType=__SheetType and SheetID=sSheetID;
delete from SheetFlow0 where SheetType=__SheetType and SheetID=sSheetID;
let __BreakPoint = 814620;
if __tflag=100 then
execute procedure ST_ychecksheet1(sSheetID);
end if;
commit work;
return 0;
end procedure;
[解决办法]
在ON EXCEPTION之前加一句试试:
let __StartWork = 0;