How to use Transaction in SQL Stored Procedure

Before we understand Transactions in SQL Server we look two scenarios.

Scenario-1:

When withdraw money from ATM machine. Either we receive the money or failed to receive the money. What does it mean? It means if we receive the money our transaction is successful and our account debited with the amount which we received through ATM machine, and if there is a problem like insufficient balance in the ATM machine or have some issue in machine then we didn’t get money. In this case we didn’t received the money nor our account balance is credited.

By looking the above scenario either we succeed in getting the money or failed. This is called Transaction.

Scenario-2:

Suppose we have a POS and any customer came to our shop and take some products. In this case we maintain our two tables where we increase the sales table and reduce the inventory with the amount of products which we sold. This is also a transaction.

Let suppose if the customer unable to pay for the products that he/she has taken, then we will not give those products to our customer and re-add the products to our stock. This process is called rolled back.

So basically the transactions maintain the integrity of the database. In transaction there are more than one commands involved. If all the commands are succeeded then transaction succeeds. If one or all commands failed then transaction failed and all the commands rolled back.

For better understanding Let’s create four tables, tblProducts, tblCustomers, tblOrders, and tblOrderDetails and then write a stored procedure.

CREATE TABLE tblCustomers
(
    CustomerID INT PRIMARY KEY NOT NULL,
    CompanyName NVARCHAR(255) NOT NULL,
    ContactName NVARCHAR(50) NOT NULL,
    CompanyAddress NVARCHAR(255) NULL,
    City NVARCHAR(20) NULL,
    Region NVARCHAR(20) NULL,
    Phone NVARCHAR(24) NULL
)
GO

CREATE TABLE tblProducts
(
    ProductID INT PRIMARY KEY NOT NULL,
    ProductName NVARCHAR(50) NOT NULL,
    QuantityPerUnit NVARCHAR(50) NOT NULL,
    UnitPrice NVARCHAR(255) NULL,
    UnitsInStock NVARCHAR(20) NULL
)
GO

CREATE TABLE tblOrders
(
    OrderID INT PRIMARY KEY NOT NULL,
    CutomerID INT REFERENCES tblCustomers(CustomerID) NULL,
    ORDERDATE DATETIME NULL
)
GO

CREATE TABLE tblOrderDetails
(
    OrderID INT FOREIGN KEY REFERENCES tblOrders(OrderID),
    ProductID INT FOREIGN KEY REFERENCES tblProducts(ProductID),
    UnitPrice DECIMAL(5,2) NULL,
    Quantity INT NULL
    CONSTRAINT PK_Product_Orders PRIMARY KEY(ProductID,OrderID)
)
GO

Let’s insert records in tblCustomer and tblProducts table, then write a procedure with transaction.

INSERT INTO tblCustomers
VALUES(101,'ABC Company','John','XYZ street Washington','Washington','East','1922222555')
GO

INSERT INTO tblProducts
VALUES(1001,'Pepsi','6 per pack','10.75',100)
GO

Now Let’s write a stored procedure to understand Transactions in SQL.

CREATE PROCEDURE usp_SalesTransaction
(
    @QuantitySold INT,
    @ProductID INT,
    @CustomerID INT
)
AS
BEGIN
    DECLARE @OrderNumber INT
    SET @OrderNumber=(SELECT ISNULL(MAX(OrderID),0) FROM tblOrders)+1
    
    DECLARE @QuantityAvailable INT
    SET @QuantityAvailable=(SELECT UnitsInStock FROM tblProducts WHERE ProductID=@ProductID)
		
    DECLARE @UnitPrice DECIMAL(5,2)
    SET @UnitPrice=(SELECT TOP 1 UnitPrice FROM tblProducts WHERE ProductID=@ProductID)
		
    IF(@QuantitySold>@QuantityAvailable)
    BEGIN
        RAISERROR ('Stock not available',16,1)
    END
    ELSE
    BEGIN
        BEGIN TRANSACTION
        BEGIN TRY
        SET NOCOUNT ON;
            UPDATE tblProducts SET UnitsInStock=UnitsInStock-@QuantitySold

            INSERT INTO tblOrders VALUES(@OrderNumber,@CustomerID,GETDATE())

            INSERT INTO tblOrderDetails VALUES(@OrderNumber,@ProductID,@UnitPrice,@QuantitySold)
		
        COMMIT TRANSACTION
        PRINT 'Transaction Successful'
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION
            PRINT 'Transaction Failed'
        END CATCH
    END
END
GO

In the above stored procedure, we created three input parameters,

@QuantitySold: how much quantity we sold to a customer

@ProductID: Which product we sold to a customer, and

@CustomerID: To whom we sold the product

Then we declared three variables:

@OrderNumber: To generate the order number for a customer

@QuantityAvailable: How much quantity we have in our stock, and

@UnitPrice: To get the unit price from the tblProducts table

Then we checked that either the quantity in our stock is more than the customer required or not. If the quantity sold is greater than quantity available, then it means we have insufficient stock. If the quantity sold is less than the quantity available then we go for further process.

If the quantity available is more than the quantity sold then we start the transaction with the keyword BEGIN TRANSACTION, and write the statements.

If there is not any problem, then the stored statements executed successfully and transactions will be committed, and if there is any problem then transaction failed and all the modified statements will be rolled back to initial state.

Let’s execute the procedure with more quantity that we sold than the available quantity.

EXECUTE usp_SalesTransaction 1000, 1001, 101

Let’s execute the procedure with less quantity that we sold than the available quantity.

EXECUTE usp_SalesTransaction 10, 1001, 101

Message in query grid view and the result of above stored procedure is as below.

Now, Let’s suppose we enter invalid product id then what will happen..

EXECUTE usp_SalesTransaction 1000, 10201, 101

By entering the invalid product id the transaction fails and there will be no change in the result set.

Buy Best Stationery Products in Pakistan

Piano Ballpoint Pen

ORO Trimetal Pencil

Dollar Clipper Pen

UHU 10×7 ml Pack of 10 PCs

Glue Stick 8g Pack Of 12 PCs

Color Pencils Box of 12 PCs

Tags: SQL Transaction, transaction, Transaction in sql, use of transaction in sql

Scenario-1:

Scenario-2:

Recommended Readings

Buy Best Stationery Products in Pakistan

Piano Ballpoint Pen

ORO Trimetal Pencil

Dollar Clipper Pen

UHU 10×7 ml Pack of 10 PCs

Glue Stick 8g Pack Of 12 PCs

Color Pencils Box of 12 PCs

You Might Also Like

Difference Between IN and NOT IN Operators in SQL

How To Select Distinct Records in SQL Server

How To Select Data From Table in SQL Server