If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: ROLLBACK on-ERROR IN A STORED PROCEDURE??

Can the stored procedure on any negitive SQLCODE number roll itself back? I'm tring not to do the rollback from the Active Server Pages (ASP) script which would cause me more "if then else error logic".
I would like all my stored procedures to work "100% & commit" or "return an error code & rolled back".

If rollback in a stored procedure is not possible then I guess I'll have to do it in the ASP code. I've been unsucessfull thus far in coding a "rollback". can someone share an asp example ?

you would think so.

Originally posted by Damian Ibbotson
Won't the SPs do this anyway?

My experience is that no the stored procedures compiled for DB v6.1 on the MVS OS 390 do not do this at least automaticly. Prehaps I have some settings wrong but when they encounter a negitive type error they do not rollback. Can someone help...

They all start with the same options:

CREATE PROCEDURE P652OG

( IN SENT_SESSIONID CHAR(40),
IN SENT_TRACKING CHAR(8),
IN SENT_OLDREQSW CHAR(1),
IN SENT_RCPTREQSW CHAR(1),
OUT TEST_NOTE VARCHAR(40),
OUT OUT_CODE INT)
FENCED

Version 6 of DB2 does not allow to use SQL ROLLBACK with the stored procedure. However, when the ROLLBACK statement got executed, the data will be rollbacked even though the SQLCODE = -751. You need to handle this SQLCODE in your stored procedure.