Sql Server Stored Procedure Transaction Handling

Database transaction handling becomes important when updating multiple tables within same stored procedure.

Example scenario would be a bank transaction imagine there are two table
tblAccount - Insert the account number details for the transaction
tblTransaction - Insert transaction details

Both tables are need to be updated so you have to handle the transaction. Lets say you did not handle the transaction then what could happen is if an error occurs after you insert data to the first table then the data will not be inserted to the second table. Inconsistency between the data can be caused due to above issue.

What need to be done is data should be inserted to the both the tables, Other wise transaction should be rolled back as data is not inserted to any of the tables. So the consistency of the data is still managed even though an error was occurred during the process.

Below example stored procedure shows you how to handle the transaction within a stored procedure along with try catch blocks.


CREATE PROCEDURE spBankTransaction
@AccountNO VARCHAR(50),
@Description VARCHAR(500),
@Type VARCHAR(20),
@Amount FLOAT
AS
BEGIN TRY
   BEGIN TRANSACTION    -- Start the transaction
 
   INSERT INTO tblAccount (AccountNO,Description)
   VALUES(@AccountNO,@Description)
 
   DECLARE @AcouuntID INT
 
   SET @AcouuntID=SCOPE_IDENTITY();
 
   INSERT INTO tblTransaction (AccountID,Type,DateTime,Amount)
   VALUES(@AcouuntID,@Type,GETDATE(),@Amount)

  COMMIT
END TRY
BEGIN CATCH
 -- There was an error
  IF @@TRANCOUNT > 0
     ROLLBACK -- Transaction is rolled back

  -- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1) -- An error shows
END CATCH