Error Handling in SQL

Introduction:

In this demo, We will understand the error handing in procedures. Lets start with demo without wasting any time in discussing something that is not so important

Demo:

Lets create the tables first

CREATE TABLE Inventory
(ProductID INT IDENTITY PRIMARY KEY NOT NULL,
Product varchar(20) NOT NULL,
Quantity int,
TotalQuantity int
)


CREATE Table Procurement
(EMPID int PRIMARY KEY NOT NULL,
ProductID int references Inventory(ProductID) NOT NULL,
Quantity int
)
INSERT INTO Inventory Values ('HP Laptop',499,500),('DELL Laptop',500,500)
,('HP Mouse',499,500),('MousePad',500,500),('Docking Station',500,500)


INSERT INTO Procurement Values(1,1,1),(2,3,1)


Select * from Inventory
Select * from Procurement

Below procedure has been written to take care of the procurement process. If any new item is being allotted to any employee then this procedure will reduce the quantity allotted From the available item in Inventory table and create an entry in procurement table But if the same item is being assigned to some employee then it will reduce the available quantity in Inventory table and update the quantity column in procurement table.

Ex: If we will assign a Dell laptop to EmpID 3 then it will decrease the available quantity of DELL Laptop by one (i.e. 499) and insert one record in procurement table EMPID=3, ProductID=2 and Quantity=1
But if we assign one more HP laptop to EmpID 1 then it will reduce the available quantity by 1 (i.e. 498) and update the quantity column from 1 to 2 for EmpID=1 and ProductID=1

CREATE PROCEDURE SPROC_Procurement (
@EMPID INT
,@productID INT
,@Quantity INT
)
AS
BEGIN
DECLARE @StockAvailable INT
SELECT @StockAvailable = Quantity
FROM Inventory
WHERE ProductID = @ProductID
IF (@StockAvailable<@Quantity)
BEGIN
RAISERROR (
'Quantity Not Available'
,16
,1
)
END
ELSE
BEGIN
BEGIN TRANSACTION
BEGIN TRY
UPDATE Inventory
SET Quantity = Quantity - @Quantity
WHERE ProductID = @ProductID
IF EXISTS (
SELECT 1
FROM Procurement
WHERE EMPID = @EMPID
AND ProductID = @ProductID
)
BEGIN
UPDATE Procurement
SET Quantity = Quantity + @Quantity
WHERE EMPID = @EMPID
AND ProductID = @ProductID
END
ELSE
BEGIN
PRINT 'HERE'
INSERT INTO Procurement
SELECT @EMPID
,@ProductID
,@Quantity
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_NUMBER() AS ERRORNumber
SELECT ERROR_MESSAGE() AS ERRORMESSAGE
SELECT ERROR_PROCEDURE() AS ERRORProcedure
SELECT ERROR_SEVERITY() AS ERRORSEVERITY
SELECT ERROR_STATE() AS ERRORState
SELECT ERROR_Line() AS ERRORLine
END CATCH
END
END

CASE 1:

We want to assign 500 HP Laptop to EmpID=4. We don’t have 500 HP Laptops available in our inventory so it should raise the error. Let’s see if our procedure is doing it.

This was taken care by below piece of code


SELECT @StockAvailable = Quantity
FROM Inventory
WHERE ProductID = @ProductID
IF (@StockAvailable<@Quantity)
BEGIN
RAISERROR (
'Quantity Not Available'
,16
,1
)
END

CASE 2:
Now suppose we want to assign DELL Laptop to EmpID=2. It should update quantity of DELL Laptop to 499 in inventory table and create a new entry of EmpID=2, ProductID=2 And Quantity=1

We have got the error message as EmpID is primary key in procurement table and we are trying to insert the duplicate value. Our procedure updated Inventory table first which completed successfully, but it throws the error when it was trying to insert a new record in procurement table, but as we have error handling in place so the update statement was also rolled back.

We can verify this by looking at our tables.

Select * from Inventory
Select * from Procurement

How does it work? Whatever we have written in our try block (BEGIN TRY and END TRY) if any statement in this block will fail, then this block will transfer the control to catch block(Begin Catch and END Cath) And the transaction will rollback inside catch block. As we may have duplicate values for EmpID so EmpID is not a good choice for a primary key. This table should have Composite primary key ON EmpID and ProductID so lets drop the primary key and create composite key.

ALTER TABLE Procurement
DROP CONSTRAINT PK_Procurem_14CCD97D8AB37702
ALTER TABLE Procurement
ADD CONSTRAINT PK_EMP_Product PRIMARY KEY(EMPID,ProductID)

Now, Run the procedure with same parameter again

And one dell laptop has been allotted to EmpID=2.

Happy Learning….!!!

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 7+ years of experience working in a variety of environments

Leave a Reply

Your email address will not be published.