Home
Stored Procedures - Do you use transactions for complicated stored procedures?
  v1.0 Posted at 13/11/2019 10:25 AM by Tiago Araujo

​A transaction means an atomic operation, it assures that all operations within the transaction are successful, if not, the transaction will cancel all operations and roll back to the original state of the database, that means no dirty data and mess exists in the database, so if a stored procedure has many steps, and each step has relation with other steps, it is strongly recommended that you encapsulate the procedure in a transaction.​

ALTER PROCEDURE [dbo].[procInit]
AS
DELETE ParaLeft
DELETE ParaRight
INSERT INTO ParaLeft (ParaID)
SELECT ParaID FROM Para

Bad example: No tran​saction here, if any of operations fail, the database will only partially update, resulting in an unwanted result

ALTER PROCEDURE [dbo].[procInit]
AS
BEGIN TRANSACTION
DELETE ParaLeft
DELETE ParaRight
INSERT INTO ParaLeft (ParaID)
SELECT ParaID FROM Para
COMMIT

Good example: Using a transaction to assure that all operations within the transaction will be successful, otherwise, the database will roll back to original state​

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: