I have been trying go get an answer to this question for sometime now. It has to do with transaction handling and stored procedures.
I can best explain with an example. Suppose I have two stored procedures: Insert_TableA and Insert_TableB. One inserts to TableA and the other to TableB.
There are times when I need to insert into both TableA and TableB within the scope of a transaction. What I have been unable to find out is if I create a third stored procedure that executes both Insert_TableA and Insert_TableB within a transaction will that work the same way as if I was doing an Insert into directly into TableA and TableB within the scope of a transaction?
In other words, will the following add a new record into TableA and TableB if there are no errors. And will the following do a rollback to both TableA and TableB if one of the inserts fails?