SQL2000和ASA 存储过程语法有什么不同？
SQL2000和ASA 存储过程语法有什么不同？
[解决办法]
sql anywhere
ALTER PROCEDURE "spectwosuite"."zu_proc_g_37"(in p_evaluationdate datetime,in p_vessel numeric(15),in p_name numeric(15))
begin
declare l_id1 numeric(15);
declare l_id2 numeric(15);
create table #tmptable(
employeeid numeric(15) null,
name varchar(200) null,
Rank varchar(200) null,
Birth datetime null,
Vessel numeric(15) null,
company varchar(50) null,
evaluationdate datetime null,
Port_joined varchar(50) null,
Date_joined datetime null,
Port_signed_off varchar(50) null,
Date_signed_off datetime null,
Jan_Jun varchar(50) null,
Jul_Dec varchar(50) null,
New_joiner varchar(50) null,
Sign_off varchar(50) null,
uOthers varchar(50) null,
coor1e varchar(50) null,
Category01 varchar(50) null,
Category02 varchar(50) null,
Category03 varchar(50) null,
Category04 varchar(50) null,
Category05 varchar(50) null,
Category06 varchar(50) null,
Category08 varchar(50) null,
Category09 varchar(50) null,
Category10 varchar(50) null,
Category11 varchar(50) null,
Category12 varchar(50) null,
Category13 varchar(50) null,
maorce varchar(50) null,
zCategory01 varchar(50) null,
zCategory02 varchar(50) null,
zCategory03 varchar(50) null,
zCategory04 varchar(50) null,
zCategory05 varchar(50) null,
zCategory06 varchar(50) null,
zCategory08 varchar(50) null,
zCategory09 varchar(50) null,
zCategory10 varchar(50) null,
zCategory11 varchar(50) null,
zCategory12 varchar(50) null,
zCategory13 varchar(50) null,
Remarks varchar(5000) null,
eng varchar(5000) null,
deck varchar(500) null,
CE varchar(5000) null,
MASTER varchar(5000) null,
CPD varchar(5000) null,
employalbe varchar(50) null,
);
insert into #tmptable( employeeid,name,rank,Birth,evaluationdate )
select distinct "employee"."employeeid","employee"."surname", "empranktype"."description" , "employee"."birthdate" , "empevaluation"."evaluationdate"
FROM "empevaluation" LEFT OUTER JOIN "employee" ON "employee"."employeeid" = "empevaluation"."employeeid" LEFT OUTER JOIN "empranktype" ON "empranktype"."empranktypeid" = "employee"."employeerankid"
where empevaluation.EMPEVALPROFILEID in (12500000009,12500000010,12500000011,12500000012);
update #tmptable
set company =(
SELECT top 1 "address"."name"
from "address" ,"empcontract" where "empcontract"."companyaddressid" = "address"."addressid"
and "empcontract"."employeeid" = #tmptable .employeeid ) ;
update #tmptable
set Vessel =(
SELECT "empevaluation"."ZU_VESSEL"
from "empevaluation" where "empevaluation"."evaluationdate" = "#tmptable"."evaluationdate"
and "#tmptable"."employeeid" = "empevaluation"."employeeid"
and "empevaluation"."ZU_VESSEL" is not null) ;
update #tmptable
set Port_joined =(
SELECT "empevaluation"."ZU_PORTJOINED"
from "empevaluation" where "empevaluation"."evaluationdate" = "#tmptable"."evaluationdate"
and "#tmptable"."employeeid" = "empevaluation"."employeeid"
and "empevaluation"."ZU_PORTJOINED" is not null) ;
update #tmptable
set Date_joined =(
SELECT "empevaluation"."ZU_DATEJOINED"
from "empevaluation" where "empevaluation"."evaluationdate" = "#tmptable"."evaluationdate"
and "#tmptable"."employeeid" = "empevaluation"."employeeid"
and "empevaluation"."ZU_DATEJOINED" is not null) ;
update #tmptable
set Port_signed_off =(
SELECT "empevaluation"."ZU_PORTSIGNEDOFF"
from "empevaluation" where "empevaluation"."evaluationdate" = "#tmptable"."evaluationdate"
and "#tmptable"."employeeid" = "empevaluation"."employeeid"
and "empevaluation"."ZU_PORTSIGNEDOFF" is not null) ;
update #tmptable
set Date_signed_off =(
SELECT "empevaluation"."ZU_DATESIGNEDOFF"
from "empevaluation" where "empevaluation"."evaluationdate" = "#tmptable"."evaluationdate"
and "empevaluation"."ZU_DATESIGNEDOFF" is not null) ;
update #tmptable
set employalbe =(
SELECT "empevaluation"."ZU_EMPLOYABLE"
from "empevaluation" where "empevaluation"."evaluationdate" = "#tmptable"."evaluationdate"
and "#tmptable"."employeeid" = "empevaluation"."employeeid"
and "empevaluation"."ZU_EMPLOYABLE" is not null) ;
update #tmptable
set Jan_Jun =(
SELECT EMPEVALRESULT.code
from EMPEVALRESULT,"empevaluation",EMPEVALSCORE
where EMPEVALRESULT.EMPEVALRESULTID=EMPEVALSCORE.EMPEVALRESULTID
and "#tmptable"."employeeid" = "empevaluation"."employeeid"
and "empevaluation"."evaluationdate" = "#tmptable"."evaluationdate"
and EMPEVALSCORE."empevaluationid" = empevaluation.empevaluationid
and empevaluation.EMPEVALPROFILEID=12500000008
and EMPEVALSCORE.EMPEVALCRITERIAID=12500000025);
update #tmptable
set Jul_Dec =(
SELECT EMPEVALRESULT.code
from EMPEVALRESULT,"empevaluation",EMPEVALSCORE
where EMPEVALRESULT.EMPEVALRESULTID=EMPEVALSCORE.EMPEVALRESULTID
and "empevaluation"."evaluationdate" = "#tmptable"."evaluationdate"
and EMPEVALSCORE."empevaluationid" = empevaluation.empevaluationid
and empevaluation.EMPEVALPROFILEID=12500000008
and EMPEVALSCORE.EMPEVALCRITERIAID=12500000026);
update #tmptable
set New_joiner =(
SELECT EMPEVALRESULT.code
from EMPEVALRESULT,"empevaluation",EMPEVALSCORE
where EMPEVALRESULT.EMPEVALRESULTID=EMPEVALSCORE.EMPEVALRESULTID
and "#tmptable"."employeeid" = "empevaluation"."employeeid"
and "empevaluation"."evaluationdate" = "#tmptable"."evaluationdate"
and EMPEVALSCORE."empevaluationid" = empevaluation.empevaluationid
and empevaluation.EMPEVALPROFILEID=12500000008
and EMPEVALSCORE.EMPEVALCRITERIAID=12500000027);
begin
declare cur1 cursor for select employeeid,evaluationdate from #tmptable;
declare tmpdate datetime;
declare tmpid numeric(15);
declare l_count int;
open cur1;
FETCH NEXT cur1 INTO tmpid,tmpdate;
while @@fetch_status=0 LOOP
set l_count=0;
select count(*) into l_count from "empevaluation"
where "empevaluation"."employeeid"=tmpid
and "empevaluation"."evaluationdate" = tmpdate
and empevaluation.EMPEVALPROFILEID=12500000009;
if l_count=1 then
set l_id1 = 12500000009;--=co
update #tmptable set coor1e ='CO' where "employeeid"=tmpid and "evaluationdate"=tmpdate;
else
set l_id1 = 12500000010;--=1e
update #tmptable set coor1e ='1E' where "employeeid"=tmpid and "evaluationdate"=tmpdate;
end if;
set l_count=0;
select count(*) into l_count from "empevaluation"
where "empevaluation"."employeeid"=tmpid
and "empevaluation"."evaluationdate" = tmpdate
and empevaluation.EMPEVALPROFILEID=12500000011;
if l_count=1 then
set l_id2 = 12500000011;--=master
update #tmptable set coor1e ='MASTER' where "employeeid"=tmpid and "evaluationdate"=tmpdate;
else
set l_id2 = 12500000012;--=ce
update #tmptable set coor1e ='CE' where "employeeid"=tmpid and "evaluationdate"=tmpdate;
end if;
FETCH NEXT cur1 INTO tmpid,tmpdate;
END LOOP;
CLOSE cur1;
end;
update #tmptable
set Category01 =(
SELECT top 1 EMPEVALRESULT.code
from EMPEVALRESULT,"empevaluation",EMPEVALSCORE
where EMPEVALRESULT.EMPEVALRESULTID=EMPEVALSCORE.EMPEVALRESULTID
and "#tmptable"."employeeid" = "empevaluation"."employeeid"
and "empevaluation"."evaluationdate" = "#tmptable"."evaluationdate"
and EMPEVALSCORE."empevaluationid" = empevaluation.empevaluationid
and empevaluation.EMPEVALPROFILEID=l_id1
and EMPEVALCRITERIAID=12500000005);
update #tmptable
set Category02 =(
SELECT EMPEVALRESULT.code
from EMPEVALRESULT,"empevaluation",EMPEVALSCORE
where EMPEVALRESULT.EMPEVALRESULTID=EMPEVALSCORE.EMPEVALRESULTID
and "#tmptable"."employeeid" = "empevaluation"."employeeid"
and "empevaluation"."evaluationdate" = "#tmptable"."evaluationdate"
and EMPEVALSCORE."empevaluationid" = empevaluation.empevaluationid
and EMPEVALPROFILEID=l_id1
and EMPEVALCRITERIAID=12500000006);
update #tmptable
set Category03 =(
SELECT EMPEVALRESULT.code
from EMPEVALRESULT,"empevaluation",EMPEVALSCORE
where EMPEVALRESULT.EMPEVALRESULTID=EMPEVALSCORE.EMPEVALRESULTID
and "#tmptable"."employeeid" = "empevaluation"."employeeid"
and "empevaluation"."evaluationdate" = "#tmptable"."evaluationdate"
and EMPEVALSCORE."empevaluationid" = empevaluation.empevaluationid
and EMPEVALPROFILEID=l_id1
and EMPEVALCRITERIAID=12500000007);
update #tmptable
set Category04 =(
SELECT EMPEVALRESULT.code
from EMPEVALRESULT,"empevaluation",EMPEVALSCORE
where EMPEVALRESULT.EMPEVALRESULTID=EMPEVALSCORE.EMPEVALRESULTID
and "#tmptable"."employeeid" = "empevaluation"."employeeid"
and "empevaluation"."evaluationdate" = "#tmptable"."evaluationdate"
and EMPEVALSCORE."empevaluationid" = empevaluation.empevaluationid
and EMPEVALPROFILEID=l_id1
and EMPEVALCRITERIAID=12500000019);
update #tmptable
set Category05 =(
SELECT EMPEVALRESULT.code
from EMPEVALRESULT,"empevaluation",EMPEVALSCORE
where EMPEVALRESULT.EMPEVALRESULTID=EMPEVALSCORE.EMPEVALRESULTID
and "#tmptable"."employeeid" = "empevaluation"."employeeid"
and "empevaluation"."evaluationdate" = "#tmptable"."evaluationdate"
and EMPEVALSCORE."empevaluationid" = empevaluation.empevaluationid
and EMPEVALPROFILEID=l_id1
and EMPEVALCRITERIAID=12500000009);
END
