A question about stored procedures and Transactions

Question

A question about stored procedures and Transactions

eichnerm

Old Hand

Points: 359
More actions
December 22, 2022 at 8:59 pm

#4129065
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?
```
Begin

      Begin Try
            Begin Tran

            Exec Insert_TableA
            Exec Insert_TableB

            Commit Tran
        End Try
        Begin Catch
              Rollback Tran
        End Catch
End
```
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101120 More actions · Answer 1

Yes, those procs will both run under the same transaction (assuming no COMMIT(s) in the procs).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

eichnerm Old Hand Points: 359 More actions · Answer 2

Thank you for the response.

I had been told by an instructor that I would have problems wrapping two stored procedures within a transaction, and that I should just copy the insert statements from each stored procedure into a new stored procedure that handles transactions.

He wasn't clear why, and although I played around with it, he told me that I couldn't rely on it working 100% of the time. He wasn't a very good teacher and although I didn't trust what he said, I was hesitant to take the chance.

Thanks again.

Michael L John One Orange Chip Points: 27060 More actions · Answer 3

Remember, there is no such thing as a nested transaction.

If there are begin/commit/rollback in either of those procs, the final commit/rollback will not occur until the "parent" proc.

eichnerm wrote:

I had been told by an instructor that I would have problems wrapping two stored procedures within a transaction, and that I should just copy the insert statements from each stored procedure into a new stored procedure that handles transactions.
He wasn't clear why, and although I played around with it, he told me that I couldn't rely on it working 100% of the time. He wasn't a very good teacher and although I didn't trust what he said, I was hesitant to take the chance.

This makes no sense. I would press him for an explanation.

Also, another consideration. WHERE to handle a transaction is something that needs to be considered. Should it be handled in the application code, in the individual procs, or something else?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

eichnerm Old Hand Points: 359 More actions · Answer 4

Thanks. I knew that, but it's always nice to get confirmation of what you know.

I am considering having a series of individual CRUD procedures that don't include transaction processing, and then creating what I will call wrapper stored procedures that call any combination of the individual CRUD procedures. These wrapper stored procedures are the only ones that contain transaction processing.

I like to include error handing in each of my stored procedures. I am curious about the following:

If I catch an error in my individual CRUD stored procedures do I need to throw the error back to the calling wrapper stored procedure so that it can rollback the transaction that was started in the wrapper stored procedure?
If I catch an error in my individual CRUD stored procedures do I need to throw the error back to the calling C# code so that the C# code can handle the error? I have friends who just let their CRUD stored procedures fail and let the calling C# code handle any errors?
Should I eliminate error handling in my individual CRUD stored procedures like my friends do?

Thanks