Various Types of Transactions in SQL Server

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

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

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

EXEC [USP_InsertEmployee] 'MAHESH' , 'MUMBAI'

SELECT * FROM dbo.employee

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

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.