the definition of Stored Proc is:
REPLACE PROCEDURE GETNEXTLOADID(OUT NEXTID INTEGER)
BEGIN
LOCKING TABLE tbl1 FOR WRITE
UPDATE tbl1
SET CNTRL_ID = CNTRL_ID + 1;
SELECT CNTRL_ID INTO :NEXTID FROM tbl1;
END;
so why don't you just use tbl1 in the update statement?
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
Hi Ulrich,
Can you please explain? The only issue is to use the output of Store Proc to update a column in another table.
Thanks,
Terankit
Its not going to work.
In your example it would not be needed as you could also
UPDATE a
FROM TABLEA a,
tbl1 t
SET CNTL_ID = T.CNTRL_ID
WHERE condition;
If tbl1 can be updated from different sessions at the same time then do the update and insert select the value in a volatile or global temp table within the SP.
Use the volatile or global temp table in your update in this case.
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
Hi All,
I have to update a coulmn (CNTL_ID) in Table A using the output of Stored Proc.
CALL GETNEXTLOADID(NEXTID) gives us the next available ID.
I tried:
UPDATE TABLEA
FROM TABLEA,
(CALL GETNEXTLOADID(NEXTID)) T(NID)
SET CNTL_ID = T.NID
WHERE condition;
But it is not working.
Please correct this query or any other way to do this.
Thanks,
Terankit