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
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.