Committing an Inner Transaction question without affecting an Outer Transaction.

Hello.

I'd like to be able to do something like the following:

begin transaction

• Do stuff exec myproc;
• Do stuff exec myproc;

commit; --OUTER BLOCK COMMIT

Now, myproc looks like this:

create procedure myproc as
begin
  insert into MyTable ....;
  commit; --PROC COMMIT
end;

I want to make sure that the insert into MyTable happens all the time, when the proc gets called from the outer block, regardless of whether the outer block commits or not. Furthermore, I don't want the PROC COMMIT to commit any DML's issued in the outer block prior to the proc commit.

How do I go about doing this?

thanks. Received on Thu Apr 21 2005 - 19:13:22 CDT