Various Types of Transactions in SQL Server

Introduction

This article explains how to use the transactions and various ways of writing the transactions in SQL Server. I have taken an example to explain this in detail. Please see the examples to understand better.

First, you need to create two tables.

Employee Table

You can use the below SQL statement to create an employee table in your SQL database.

CREATE TABLE [dbo].[employee] (
      [ID] [INT] IDENTITY (1, 1) NOT NULL
    , [Name] [VARCHAR](50) NULL
    , [Address] [VARCHAR](50) NULL
)

The Above SQL statement creates a table named "employee" in a database with three columns: "ID," "Name," and "Address." The ID column is set as an identity column, starting at one and incrementing by 1. It is also set as the primary key and not null. The Name and Address columns are of VARCHAR data type, with a maximum length of 50 characters, and can contain null values.

Department Table

You can use the below SQL statement to create a department table in your SQL database.

CREATE TABLE [dbo].[Department] (
      [ID] [INT] IDENTITY (1, 1) NOT NULL
    , [Department] [VARCHAR](50) NULL
)

The above SQL statement creates a table named "Department" in a database, with two columns: "ID" and "Department." The "Department" columns are of VARCHAR data type, with a maximum length of 50 characters, and can contain null values. The ID column is set as an identity column, starting at one and incrementing by 1. It is also set as the primary key and not null.

1. SIMPLE TRANSACTION.

A simple transaction in SQL is a sequence of one or more SQL statements that are executed as a single unit of work. The main purpose of using transactions is to ensure data integrity and consistency in a database. The transaction ensures that either all the statements are executed successfully, or none of them are executed.

Now, I am inserting the data into the employee table. I am using TRAN1 as my transaction. Further, I am committing the transition in a try block and rolling back the same transaction in a catch block. Here there is no error, so the transaction is committed.

CREATE PROCEDURE [dbo].[USP_InsertEmployee]
      @Name VARCHAR(50)
    , @Address VARCHAR(50) = NULL
AS
BEGIN
  SET
  NOCOUNT ON;
  BEGIN TRAN TRAN1

    BEGIN TRY
      INSERT INTO [dbo].[employee] ([Name], [Address])
      VALUES (@Name, @Address)
    COMMIT TRANSACTION TRAN1
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION TRAN1
  END CATCH

END

The above SQL statement creates a stored procedure named "USP_InsertEmployee" that takes in two parameters: @Name and @Address. The @Name parameter is a VARCHAR data type with a maximum length of 50 characters, and it is required to execute the procedure. The @Address parameter is a VARCHAR data type with a maximum length of 50 characters, and it's set to null by default.

The procedure then starts a transaction named "TRAN1". The BEGIN TRY block is used to execute the INSERT statement, which inserts a new row into the "employee" table with the values of the @Name and @Address parameters. If the INSERT statement is successful, the transaction is committed using the COMMIT TRANSACTION statement. In case of any error in the TRY block, the CATCH block will be executed, which rolls back the transaction using the ROLLBACK TRANSACTION statement, undoing any changes made in the TRY block. Finally, the procedure ends with the END statement.

This procedure can insert a new employee record into the "employee" table by passing the name and address values as parameters.

Execute Stored Procedure

To execute the above-created stored procedure, use the below SQL statement.

EXEC [USP_InsertEmployee] 'MAHESH' , 'MUMBAI'

SELECT * FROM dbo.employee

The above SQL statement executes the stored procedure "USP_InsertEmployee" we created earlier, passing in the values' MAHESH' and 'MUMBAI' as the @Name and @Address parameters, respectively. This would insert a new row into the "employee" table with the values' MAHESH' and 'MUMBAI' in the "Name" and "Address" columns, respectively.

The second line of code is a SELECT statement that retrieves all the rows from the "employee" table. After the execution of the stored procedure, running this SELECT statement would return a result set that includes the new row that was inserted into the table with the values' MAHESH,' and 'MUMBAI' in the "Name" and "Address" columns, respectively and the result is shown in below figure-1

Figure-1

2. SIMPLE NESTED TRANSACTIONS

A nested transaction in SQL is a transaction that is started within an already active transaction. Nested transactions are helpful when you want to divide a complex transaction into smaller, more manageable parts. Each nested transaction can be treated as a separate unit of work and can be committed or rolled back independently of the other nested transactions.

Now I am updating the already created stored procedure in a simple nested transaction type stored procedure.

ALTER PROCEDURE [dbo].[USP_InsertEmployee]
      @Name VARCHAR(50)
    , @Address VARCHAR(50) = NULL
    , @Department VARCHAR(50) = NULL
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRAN TRAN1

    BEGIN TRY
      INSERT INTO [dbo].[employee] ([Name], [Address])
      VALUES (@Name, @Address)
      BEGIN TRAN TRAN2

        BEGIN TRY
          INSERT INTO [dbo].[Department] ([Department])
          VALUES (@Department)
        COMMIT TRANSACTION TRAN2
      END TRY
      BEGIN CATCH
        ROLLBACK TRANSACTION TRAN2
      END CATCH

    COMMIT TRANSACTION TRAN1
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION TRAN1
  END CATCH

END

The above SQL statement creates a stored procedure named "USP_InsertEmployee" that uses three parameters: @Name, @Address, and @Department. The @Name parameter is a VARCHAR data type with a maximum length of 50 characters, and it is required to execute the procedure. The @Address parameter is a VARCHAR data type with a maximum length of 50 characters, and it's set to null by default. The @Department parameter is a VARCHAR data type with a maximum length of 50 characters, and it's set to null by default.

Then starts a transaction named "TRAN1". The BEGIN TRY block is used to attempt to execute the INSERT statement, which inserts a new row into the "employee" table with the values of the @Name and @Address parameters.

Another transaction named "TRAN2" is then started, and another INSERT statement is executed, which inserts a new row into the "Department" table with the value of the @Department parameter.

If both insert statements are executed successfully, the transactions are committed using the COMMIT TRANSACTION statement, making the changes permanent in the database.

In case of any error in the TRY block, the CATCH block will be executed, which rolls back the transaction using the ROLLBACK TRANSACTION statement, undoing any changes made in the TRY block. Finally, the procedure ends with the END statement.

This procedure can insert a new employee record into the "employee" table and a new department record into the "Department" table by passing the name, address, and department values as parameters.

Execute Stored Procedure

To execute the above-created stored procedure, use the below SQL statement.

EXEC [USP_InsertEmployee] 'MAHESH','MUMBAI','SCIENCE'

SELECT * FROM EMPLOYEE;
SELECT * FROM DEPARTMENT;

The above SQL statement executes a stored procedure called "USP_InsertEmployee" with the parameters 'MAHESH,' 'MUMBAI,' and 'SCIENCE.' Then SQL SELECT statement is used to retrieve all data from a table called "EMPLOYEE," and the other SQL SELECT statement is used to retrieve all data from a table called "DEPARTMENT," as shown in figure 2.

Figure-2

3. TWO SEPARATE TRANSACTIONS.

In SQL, a transaction is a sequence of one or more SQL statements executed as a single unit of work. To perform two separate transactions, you would need to open a new transaction after the first one is committed or rolled back.

You can use the BEGIN TRANSACTION statement to start a new transaction and the COMMIT or ROLLBACK statement to end the current transaction.

ALTER PROCEDURE [dbo].[USP_InsertEmployee]
      @Name VARCHAR(50)
    , @Address VARCHAR(50) = NULL
    , @Department VARCHAR(50) = NULL
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRAN TRAN1

    BEGIN TRY
      INSERT INTO [dbo].[employee] ([Name], [Address])
      VALUES (@Name, @Address)
    COMMIT TRANSACTION TRAN1
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION TRAN1
  END CATCH

  BEGIN TRAN TRAN2

    BEGIN TRY
      INSERT INTO [dbo].[Department] ([Department])
      VALUES (@Department)
    COMMIT TRANSACTION TRAN2
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION TRAN2
  END CATCH

END

The above-stored procedure is named "USP_InsertEmployee" in SQL. It accepts three parameters: Name, Address, and Department. The process starts by setting NOCOUNT ON, meaning it will not display the count of rows affected by T-SQL statements.

The procedure then starts two separate transactions, named TRAN1 and TRAN2. Within each transaction, it tries to insert data into two different tables: employee and department. If the insert is successful, it commits the transaction. Otherwise, it rolls back the transaction.

So, this stored procedure tries inserting data into two different tables. If any error occurs while inserting into any tables, it will roll back the transaction. Figure 3 will clear you the part of both transaction statements.

Figure-3

Execute Stored Procedure

To execute the above-created stored procedure, use the below SQL statement.

EXEC [USP_InsertEmployee] 'MAHESH','MUMBAI','SCIENCE'

SELECT * FROM EMPLOYEE;
SELECT * FROM DEPARTMENT;

The above SQL statement executes a stored procedure called "USP_InsertEmployee" with the parameters 'MAHESH,' 'MUMBAI,' and 'SCIENCE.' Then SQL SELECT statement is used to retrieve all data from a table called "EMPLOYEE" and the other SQL SELECT statement to retrieve all data from a table called "DEPARTMENT," as shown in figure-4.

Figure-4

4. TRANSACTION WITHIN A STORED PROCEDURE.

First, create a new procedure with the name USP_InsertDepartment to insert the department name into the department table; then, you can use this procedure in another system to insert the data into the department table.

CREATE PROCEDURE [dbo].[USP_InsertDepartment]
    @Department VARCHAR(50)
AS
BEGIN
  SET
  NOCOUNT ON;
  BEGIN TRAN TRAN1

    BEGIN TRY
      INSERT INTO [dbo].[Department] ([Department])
      VALUES (@Department)
    COMMIT TRANSACTION TRAN1
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION TRAN1
  END CATCH

END

The above SQL stored procedure can insert a new department into a database table called "Department." The procedure takes one input parameter, @Department, which is a varchar(50) representing the name of the department to be inserted. The procedure uses a try-catch block to handle any errors that may occur during the insertion, and if an error does occur, it will rollback the transaction.

ALTER PROCEDURE [dbo].[USP_InsertEmployee]
      @Name VARCHAR(50)
    , @Address VARCHAR(50) = NULL
    , @Department VARCHAR(50) = NULL
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRAN TRAN1

    BEGIN TRY
      INSERT INTO [dbo].[employee] ([Name], [Address])
      VALUES (@Name, @Address)
     
     EXEC [USP_InsertDepartment] @Department

    COMMIT TRANSACTION TRAN1
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION TRAN1
  END CATCH

END

The above SQL stored procedure inserts a new employee into a table called "employee" in a database. The procedure takes three input parameters, @Name, @Address, and @Department. The first two are required, while the last one is used in the stored procedure USP_InsertDepartment that is defined inside it. The procedure uses a try-catch block to handle any errors that may occur during the insertion, and if an error does occur, it will rollback the transaction. The procedure also calls another stored procedure called USP_InsertDepartment, which is used to insert a new department.

Execute Stored Procedure

To execute the above-created stored procedure, use the below SQL statement.

EXEC [USP_InsertEmployee] 'MAHESH','MUMBAI','SCIENCE'

SELECT * FROM EMPLOYEE;
SELECT * FROM DEPARTMENT;

The above SQL statement executes a stored procedure called "USP_InsertEmployee" with the parameters 'MAHESH,' 'MUMBAI,' and 'SCIENCE.' Then SQL SELECT statement is used to retrieve all data from a table called "EMPLOYEE," and the other SQL SELECT statement is used to retrieve all data from a table called "DEPARTMENT," as shown in figure-5.

Figure-5

Conclusion

This article taught us how to use the transactions and various ways of writing the transactions in SQL Server.