Donald Duck
>
> Hi,
>
> i'm trying to write a Stored Procedure to copy all records
> of a temporary table to another table.
> Everytime i execute the proc i get the Errormessage 'Row not found'.
> If I only try to copy the first record it works fine.
> It will be nice if somebody will help me with this problem.
> Here's the code of the proc:
>
> --------------------------------------------------------------
> --------------------------------
> create dbproc procSaveBerufserfahrungen(IN Skillprofilnr fixed(10))
> As
> Var
> intBerfnr integer;
> intNEWBerfnr integer;
> intBranchenkz integer;
> intRollenkz integer;
> fixSkillprofilnr fixed(10);
> vchrBranchenbez varchar(100);
> vchrRollenbez varchar(50);
> vchrFirma varchar(100);
> vchrTaetigkeit varchar(2000);
> dteBEvon date;
> dteBEbis date;
> Try
> Declare curBerufserfahrungen Cursor For
> select *
> from "DBA_SKILL".temp_tBerufserfahrungen
> where skillprofilnr = :Skillprofilnr;
>
> Fetch First curBerufserfahrungen Into :intBerfnr,
>
> :fixSkillprofilnr,
>
> :vchrBranchenbez,
>
> :vchrRollenbez,
>
> :vchrFirma,
>
> :vchrTaetigkeit,
>
> :dteBEvon,
>
> :dteBEbis;
> If intBerfnr = -1 Then
> call "DBA_SKILL".procGetBerfnr(:intNEWBerfnr)
> Else
> set intNEWBerfnr = intBerfnr;
>
> call "DBA_SKILL".procGetBranchenkz(:vchrBranchenbez,
> :intBranchenkz);
>
> call "DBA_SKILL".procGetRollenkz(:vchrRollenbez, :intRollenkz);
>
> insert into "DBA_SKILL".tBerufserfahrung (Berfnr,
>
>
> Skillprofilnr,
>
>
> Branchen_kz,
>
>
> Rollen_kz,
>
>
> Firma,
>
>
> Taetigkeit,
>
>
> BEVon,
>
>
> BEBis)
> values (:intNEWBerfnr,
> :fixSkillprofilnr,
> :intBranchenkz,
> :intRollenkz,
> :vchrFirma,
> :vchrTaetigkeit,
> :dteBEvon,
> :dteBEbis);
>
> While ($rc <> 100) Do
> Begin
> Fetch Next curBerufserfahrungen Into :intBerfnr,
>
> :fixSkillprofilnr,
>
> :vchrBranchenbez,
>
> :vchrRollenbez,
>
> :vchrFirma,
>
> :vchrTaetigkeit,
>
> :dteBEvon,
>
> :dteBEbis;
> If intBerfnr = -1 Then
> call "DBA_SKILL".procGetBerfnr(:intNEWBerfnr)
> Else
> set intNEWBerfnr = intBerfnr;
>
> call "DBA_SKILL".procGetBranchenkz(:vchrBranchenbez,
> :intBranchenkz);
>
> call "DBA_SKILL".procGetRollenkz(:vchrRollenbez, :intRollenkz);
>
> insert into "DBA_SKILL".tBerufserfahrung (Berfnr,
>
>
> Skillprofilnr,
>
>
> Branchen_kz,
>
>
> Rollen_kz,
>
>
> Firma,
>
>
> Taetigkeit,
>
>
> BEVon,
>
>
> BEBis)
> values (:intNEWBerfnr,
> :fixSkillprofilnr,
> :intBranchenkz,
> :intRollenkz,
> :vchrFirma,
> :vchrTaetigkeit,
> :dteBEvon,
> :dteBEbis);
>
> End;
>
> Close curBerufserfahrungen;
>
> Catch
> If $rc <> 0 Then Stop ($rc, $errmsg);
> --------------------------------------------------------------
> -----------------------------------
Hi,
what you are doing is:
DECLARE .. CURSOR
FETCH FIRST
call 2 other procs
INSERT
Loop while $rc <> 100 {
FETCH
call 2 other procs
INSERT
}
Unfortunately you are checking the returncode of the
insert, not of the fetch.
Every sql statements results in setting $rc, not even
the fetch.
On the other hand you are using TRY...CATCH and will
stop with every $rc <> 0, even with 100
--> the first FETCH resulting in 100 will not
call those 2 other procs, will not insert (correct),
but will caught by CATCH and will return error 100
as the final result of your dbproc.
I think, at least the the CATCH-clause should be extended
to handle error 100 as expected (to throw away).
Further: It is not necessary to seperate the first and all the
next fetches. Even if FETCH NEXT is used, the first row
will be returned correctly --> avoid those doubling of
the call of the other 2 procs and the insert.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
