RaiseError in SQL Server

RaiseError in SQL Server with Example

In this article, I am going to discuss the RaiseError in SQL Server and along with @@ERROR using one real-time example. Please read our previous article before proceeding to this article where we discussed Transaction Management in SQL Server with some example.

Please use below SQL Script to create and populate the Product and ProductSales table with some test data.

-- Create Product table
CREATE TABLE Product
(
  ProductId INT PRIMARY KEY,
  Name VARCHAR(50),
  Price INT,
  QuantityAvailable INT
)
GO

-- Populate the Product Table with some test data
INSERT INTO Product VALUES(101, 'Laptop', 1234, 100)
INSERT INTO Product VALUES(102, 'Desktop', 3456, 50)
INSERT INTO Product VALUES(103, 'Tablet', 5678, 35)
INSERT INTO Product VALUES(104, 'Mobile', 7890, 25)
GO

-- Create ProductSales table
CREATE TABLE ProductSales
(
  ProductSalesId INT PRIMARY KEY,
  ProductId INT,
  QuantitySold INT
) 
GO

SELECT * FROM Product will give us the below output

RaiseError in SQL Server

Create the following stored procedure for product sales.

CREATE PROCEDURE spSellProduct
@ProductID INT,
@QuantityToSell INT
AS
BEGIN
  -- First we need to Check the stock available for the product we want to sell
  DECLARE @StockAvailable INT

  SELECT @StockAvailable = QuantityAvailable 
  FROM Product 
  WHERE ProductId = @ProductId

  -- We need to throw an error to the calling application 
  -- if the stock is less than the quantity we want to sell
  IF(@StockAvailable< @QuantityToSell)
  BEGIN
    Raiserror('Enough Stock is not available',16,1)
  END
  -- If enough stock is available
  ELSE
  BEGIN
    -- We need to start the transaction
    BEGIN TRANSACTION

        		-- First we need to reduce the quantity available
    UPDATE	Product SET 
        QuantityAvailable = (QuantityAvailable - @QuantityToSell)
        WHERE	ProductID = @ProductID

    -- Calculate MAX ProductSalesId
    DECLARE @MaxProductSalesId INT
    SELECT	@MaxProductSalesId = CASE 
        WHEN  MAX(ProductSalesId) IS NULL THEN 0 
        ELSE MAX(ProductSalesId) 
        END 
    FROM ProductSales

-- Increment @MaxProductSalesId by 1, so we don't get a primary key violation
Set @MaxProductSalesId = @MaxProductSalesId + 1

    -- We need to insert the quantity sold into the ProductSales table
    INSERT INTO ProductSales(ProductSalesId, ProductId, QuantitySold)
    VALUES(@MaxProductSalesId, @ProductId, @QuantityToSell)

    COMMIT TRANSACTION
  End
END

The above-stored procedure accepts 2 parameters – ProductID and QuantityToSell. The ProductID parameter specifies the product that we want to sell and the QuantityToSell parameter specifies the quantity that we want to sell. 

The problem with the above-stored procedure is that the transaction is always going to be committed even though there is an error somewhere between updating the Product table and inserting into the ProductSales table.

The main purpose of wrapping these 2 statements (Update Product Statement & Insert into ProductSales statement) in a transaction is to ensure that, both of these statements are treated as a single unit.

For example, if we have an error when executing the second statement, then the first statement should be rolled back.  

ALTER PROCEDURE spSellProduct
@ProductID INT,
@QuantityToSell INT
AS
BEGIN
  -- First we need to Check the stock available for the product we want to sell
  DECLARE @StockAvailable INT

  SELECT @StockAvailable = QuantityAvailable 
  FROM Product 
  WHERE ProductId = @ProductId

  -- We need to throw an error to the calling application 
  -- if the stock is less than the quantity we want to sell
  IF(@StockAvailable< @QuantityToSell)
  BEGIN
    Raiserror('Enough Stock is not available',16,1)
  END
  -- If enough stock is available
  ELSE
  BEGIN
    -- We need to start the transaction
    BEGIN TRANSACTION

       		 -- First we need to reduce the quantity available
    UPDATE	Product SET 
        QuantityAvailable = (QuantityAvailable - @QuantityToSell)
        		WHERE	ProductID = @ProductID

    -- Calculate MAX ProductSalesId
    DECLARE @MaxProductSalesId INT
    SELECT	@MaxProductSalesId = CASE 
        WHEN  MAX(ProductSalesId) IS NULL THEN 0 
        ELSE MAX(ProductSalesId) 
        END 
    FROM ProductSales

-- Increment @MaxProductSalesId by 1, so we don't get a primary key violation
Set @MaxProductSalesId = @MaxProductSalesId + 1

    -- We need to insert the quantity sold into the ProductSales table
    INSERT INTO ProductSales(ProductSalesId, ProductId, QuantitySold)
    VALUES(@MaxProductSalesId, @ProductId, @QuantityToSell)

    -- The @@Error returns a NON-ZERO value if there is an error, otherwise it will return ZERO, 
    -- indicating that the previous SQL statement encountered no errors
    IF(@@ERROR <> 0)
    BEGIN
      ROLLBACK TRANSACTION
      PRINT 'Rolled Back the Transaction'
    END
    ELSE
    BEGIN
      COMMIT TRANSACTION
      PRINT 'Committed the Transaction'
    END
  End
END

In the above procedure, if you comment the line (Set @MaxProductSalesId = @MaxProductSalesId + 1), and then execute the stored procedure there will be a primary key violation error when trying to insert into the ProductSales table as a result of which the entire transaction will be rolled back.

Note:

The @@ERROR is cleared and reset on each statement execution. Check it immediately following the statement being verified, or save it to a local variable that can be checked later.

In the Product table, we already have a record with ProductID = 4. So the insert statement causes a primary key violation error. The @@ERROR retains the error number, as we are checking for it immediately after the statement that causes the error.

INSERT INTO Product values(4, 'Mobile Phone', 1500, 100)
IF(@@ERROR <> 0)
     PRINT 'Error Occurred'
ELSE
     PRINT 'No Errors'

On the other hand, when you execute the code below, you will get the message ‘No Errors’. This is because the @@ERROR is cleared and reset on each statement execution. 

INSERT INTO Product values(4, 'Mobile Phone', 1500, 100)
-- At this point the @@ERROR will have a NON ZERO value 
SELECT * FROM Product
-- At this point the @@ERROR reset to ZERO, because the 
-- select statement successfully executed
IF(@@ERROR <> 0)
     PRINT 'Error Occurred'
ELSE
     PRINT 'No Errors'

In the below example, we are storing the value of @@Error function to a local variable, which is then used later.

DECLARE @Error INT
INSERT INTO Product VALUES(4, 'Mobile Phone', 1500, 100)
Set @Error = @@ERROR
SELECT * FROM Product
IF(@Error <> 0)
     PRINT 'Error Occurred'
ELSE
     PRINT 'No Errors'

In the next article, I am going to discuss how to raise errors explicitly in SQL Server and also we will discuss the different options that we can use with Raiserror function. Here, in this article, I try to explain the RaiseError in SQL Server along with @@ERROR step by step with some examples.

Leave a Reply

Your email address will not be published. Required fields are marked *