In SQL Server 2012, Microsoft introduced the THROW statement to throw error/exception during the execution of T-SQL program which will transfer the execution control to a CATCH block.
Earlier, we were using RAISERROR() function to throw the user defined error/exception in SQL Server. Let's see how we were using RAISERROR() statement to throw the error/exception.
For this demonstration, I am going to use Northwind database and the scenario is also build against the same database.
CREATE PROCEDURE PlaceOrder
(
@p_CustomerID VARCHAR(10),
@p_ProductID INT,
@p_RequiredQuantity INT
)
AS
BEGIN
DECLARE @v_ProductName VARCHAR(50)
DECLARE @v_UnitsInStack INT
DECLARE @v_CompanyName VARCHAR(20)
SELECT @v_ProductName=ProductName,@v_UnitsInStack=UnitsInStock FROM Products WHERE ProductID=@p_ProductID
SELECT @v_CompanyName=CompanyName FROM Customers WHERE CustomerID=@p_CustomerID
BEGIN TRY
IF(@p_RequiredQuantity>@v_UnitsInStack)
BEGIN
PRINT 'Required Quantity is more than the available stock. Please refill the stock for the Order placed by the company - ' + @v_CompanyName;
THROW 54600,'Required Quantity is more than the available stock.',12
END
END TRY
BEGIN CATCH
THROW
END CATCH
END