Is This Good Writen Transaction In Stored Procedure

This is the first time that I use transactions and I just wonder am I make this right. Should I change something? I insert post(wisp). When insert post I need to generate ID in com

Solution 1:

You'd use TRY/CATCH since SQL Server 2005+

Your rollback goes into the CATCH block but your code looks good otherwise (using SCOPE_IDENTITY() etc). I'd also use SET XACT_ABORT, NOCOUNT ON

This is my template: Nested stored procedures containing TRY CATCH ROLLBACK pattern?

Edit:

• This allows for nested transactions as per DeveloperX's answer
• This template also allows for higher level transactions as per Randy's comment

Solution 2:

i think its not good all the time ,but if you want to use more than one stored procedure same time its not good be cause each stored procedure handles the transaction independently

but in this case,you should use try catch block , for exception handling , and preventing keeping transaction open on when an exception raising

Solution 3:

I've never considered it a good idea to put transactions in a stored procedure. I think it's much better to start a transaction at a higher level so that can better coordinate multiple database (e.g. stored procedure) calls and treat them all as a single transaction.