Mark Wong wrote : >> >Are all statements in a stored procedure considered a single transaction >> >where a commit is implicitly called when the stored procedure exits? >> >This is assuming no commits or rollbacks are explicitly called in the >> >body of the stored procedure. >> >> No, stored procedure calls behave like any other sql statement, i.e. they >> are >> NOT committed implicitly after the call. >> Note : The <commit statement> and <rollback statement> are NOT permitted in >> a >> stored procedure.
>Perhaps my question was a little unclear. I was attempting to ask >whether commits are implicitly called after a <routine_sql_statement> in >a stored procedure. Your response implies that they are not, athough it >addresses calls to stored procedures. No, commits are NOT implicitly called after a <routine_sql_statement>. >So if I have a series of inserts in a stored procedure, is there no way >to force a commit after each insert? There is no way. As every other sql statement a stored procedure call should be atomic in the sense, that the call does everything or nothing. You cannot guarantee this, if you commit some actions executed in a procedure and if an error occurs afterwards. IMO every stored procedure therefore should open a subtransaction which is ended successfully only if no unexpected error occurs. Otherwise the subtransaction should be rolled back : CREATE DBPROC EXAMPLE AS ... SUBTRANS BEGIN; ... IF no_error THEN SUBTRANS END ELSE SUBTRANS ROLLBACK; Thomas --- Thomas Anhaus SAPDB, SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
