---

Search posts under SQL Server:

---

Transaction in stored procedure in SQL Server

How to use transaction in stored procedure in SQL Server?

Simply put transaction is used to ensure that either all SQL statements gets executed successfully or no one gets executed successfully. If we have more than one SQL statements in execute in the stored procedure and we want to rollback any changes done by any one of the SQL statements in case an error occurred because of one of the SQL statements, we can use transaction in stored procedure.

Below is the stored procedure that is trying to insert a record into PersonalDetails and Accounts table using two INSERT statement. Our scenario should be that If any of the INSERT statements fails to execute, no record should be inserted into any of the table.

CREATE PROCEDURE [dbo].[InsertPersonalDetailsAndAccount]
       -- Add the parameters for the stored procedure here
       @FirstName varchar(50),
       @LastName varchar(50),
       @Age smallint,
       @Active bit,
       @Salary money,
       @PPFDeduction money
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;

       BEGIN TRAN
              BEGIN TRY
                     -- Insert into PersonalDetails table first
                     INSERT INTO PersonalDetails
                            (FirstName, LastName, Age, Active)
                     VALUES
                            (@FirstName, @LastName, @Age, @Active)

                     DECLARE @pdId int
                     SET @pdId = SCOPE_IDENTITY()
                     -- now insert into Accounts table
                     INSERT INTO Accounts
                            (Salary, PPFDeduction, PersonalDetailsId)
                     VALUES
                            (@Salary, @PPFDeduction, @pdId)
                     
              -- if not error, commit the transcation
              COMMIT TRANSACTION
       END TRY
       BEGIN CATCH
              -- if error, roll back any chanegs done by any of the sql statements
              ROLLBACK TRANSACTION
       END CATCH
END

Notice the BEGIN TRAN statement that is creating a transaction scope. After that we are using BEGIN TRY statement where we are going to keep our INSERT statements that may throw errors. After both INSERT statements, we are calling COMMIT TRANSACTION statements to notify that everything is  alright and the data can be saved into the database permanently. If any error occurs in any of the INSERT statements inside the BEGIN TRY block, the BEGIN CATCH block executes that calls ROLLBACK TRANSACTION. This will rollback any changes done in the database because of these two INSERT statements inside the TRY BLOCK.