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 debited.
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.
Recommended Readings
- Advanced SQL Queries For Practice With Solution
- SQL Queries For Practice With Solution
- SQL Interview Questions and Answers
- STORED PROCEDURE in SQL Server
- How To Join Tables Data in SQL Server
- How to use Transaction in SQL Stored Procedure
- Difference Between IN and NOT IN Operators in SQL
- How To Modify Date in SQL Using DATEADD
- How To Get Year From Date in SQL Server
- How To Get Month From Date in SQL Server
- How To Get Day From Date in SQL Server
- How To Use ROW_NUMBER Function in SQL
- Date and Time Functions in SQL Server
- How To Find Nth Highest Salary in SQL Server
- How to Backup Table Using SELECT INTO Statement
- How To Use HAVING Clause in SQL Server
- Aggregate Functions in SQL Server
- How To Group Data Using Group By in SQL Server
- How To Truncate Table in SQL Server
- How To Delete Data From Table in SQL Server
- How To Update Table Data in SQL Server
- How To Sort Data Using Order By Clause in SQL
- How To Select Distinct Records in SQL Server
- How to Filter Data From Table in SQL Server
- Round Off Values Using Ceiling and Floor in SQL
- How To Find Square Root Of A Number in SQL Server
- How To Select Data From Table in SQL Server
- How To Insert Data in SQL Server Table
- How To Add NOT NULL Constraint in SQL Server
- How To Add Check Constraint on SQL Server Table
- How To Add Default Constraint on SQL Server Table
- Unique Key Constraint in SQL Server
- How to add Foreign Key Constraint in SQL Server
- How To Add Identity To SQL Server Table Column
- How to add Primary Key Constraint in SQL Server
- How To Create Alter and Drop Table in SQL Server
- How To Create Alter and Drop Database in SQL