Throw Statement in SQL Server 2012

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.
Hypothetical Scenario - Customers in a Northwind database can place an order for a specific product or for multiple products. In case, the asking Order Quantity is more than the available stock for a specific product, a user defined exception should be raised and a notification sent to stock department to refill the stock for the required quantity for the order which needs to be processed.

To implement the above scenario, we will create a Stored Procedure, which will take the details of Customers using CustomerID, the Product using ProductID and required quantity. We will then check the available stock against the Quantity and accordingly we will throw the user defined exception, if the required quantity is more than the available quantity.

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
Now let's use the THROW statement to throw the user defined exception instead of RAISERROR() function.

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
In the above stored procedure, we are throwing a user defined exception using the new THROW statement in SQL Server 2012.

THROW uses the following parameters -

THROW Error_number, message, state

If you want the re-throw the exception, then in a catch block, you can use THROW without any parameters. This will throw the exception that was caught. Also make a note that the statement before the THROW must end with semicolon.

The output of the RAISERROR() function is as below -

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook