THROW versus RAISERROR

Error handling is just as important as verifying that your code runs and produces correct results. For developers, error handling is last chance to prepare code to fail gracefully instead of just blowing up and having to pick up pieces later.

When it comes to SQL Server, there are few options for error handling. Basics of error handling are wrapping the code that is likely to fail inside the TRY block and then dealing with failures inside the CATCH block.

Let's look at a most basic scenario below.

 
SELECT 1 / 0

SELECT 1

The first statement generates the error division by 0, but the second statement still executed as nothing is controlling what happens when errors encountered. In some cases, this is perfectly fine, but in others, we want to stop the execution of our script in the event of encountered errors. We might even want to roll back the transaction to prevent partial data change.

Now if we take the same code and place it inside TRY block we get different behavior. In this case, the first statement generates the error but the second statement is not run.

 
BEGIN TRY
SELECT 1 / 0

SELECT 1
END TRY
BEGIN CATCH

END CATCH

---

Now that we covered the basics let's look at controlling a flow of the code through the use of RAISERROR and THROW. In some cases, we need to fail code based on business rules which pass syntax test. For example, if our procedure is responsible for withdrawing money from an account, we might have a business rule that prevents going into negative. For the first example, we are just going to try to withdraw money.

 
CREATE PROCEDURE dbo.WithdrawMoney
    @AccountId INT
  , @Amount    DECIMAL(18, 2)
AS
    BEGIN
        UPDATE  dbo.Accounts
        SET Amount = Amount - @Amount
        WHERE   AccountID = @AccountId
    END

In this example we can keep withdrawing money until arighmetic overflow error will be thrown for creating large negative number. Since we want to avoid negative balance we are going to add code to where clause.

 
CREATE PROCEDURE dbo.WithdrawMoney
    @AccountId INT
  , @Amount    DECIMAL(18, 2)
AS
    BEGIN
        UPDATE  dbo.Accounts
        SET Amount = Amount - @Amount
        WHERE   AccountID              = @AccountId
                AND (Amount - @Amount) > 0
    END

Now, whenever you try to withdraw money that results in negative amount update will "fail." This failure is only reflected by the number of rows affected. In this instance, it is zero. Now we need something in front end application that reads the number of rows affected by stored procedure to understand what happened.

Another way of handling this would be to run a check first and to generate the error that is sent back to front end. So we write two stored procedures one using RAISERROR and another one using THROW and expect the same result.

 
CREATE PROCEDURE dbo.WithdrawMoney_RAISERROR
    @AccountId INT
  , @Amount    DECIMAL(18, 2)
AS
    BEGIN
        IF EXISTS
            (
                SELECT  1
                FROM    dbo.Accounts
                WHERE   AccountID              = @AccountId
                        AND (Amount - @Amount) > 0
            )
            RAISERROR( 'Insufficient Amount in account to perform withdrawal', 16, 1 )

        UPDATE  dbo.Accounts
        SET Amount = Amount - @Amount
        WHERE   AccountID = @AccountId
    END
GO
CREATE PROCEDURE dbo.WithdrawMoney_THROW
    @AccountId INT
  , @Amount    DECIMAL(18, 2)
AS
    BEGIN
        IF EXISTS
            (
                SELECT  1
                FROM    dbo.Accounts
                WHERE   AccountID              = @AccountId
                        AND (Amount - @Amount) > 0
            )
            THROW 51000, 'Insufficient Amount in account to perform withdrawal', 1

        UPDATE  dbo.Accounts
        SET Amount = Amount - @Amount
        WHERE   AccountID = @AccountId
    END

Upon executing the first procedure, we get the error message back to the front end, but after checking balance, we find that money withdrawn from the account, but in the case of the second procedure, the same error returned to the front end but money still there.

Now we begin to scratch our head trying to figure out why we lost the money even though we got errors in both cases. The truth behind is the fact that RAISERROR does not stop the execution of code if it is outside of TRY CATCH block. To get same behavior out of RAISERROR, we would need to rewrite procedure to look something like following example.

 
CREATE PROCEDURE dbo.WithdrawMoney_RAISERROR
    @AccountId INT
  , @Amount    DECIMAL(18, 2)
AS
    BEGIN
        BEGIN TRY
            IF EXISTS
                (
                    SELECT  1
                    FROM    dbo.Accounts
                    WHERE   AccountID              = @AccountId
                            AND (Amount - @Amount) > 0
                )
                RAISERROR( 'Insufficient Amount in account to perform withdrawal', 16, 1 )


            UPDATE  dbo.Accounts
            SET Amount = Amount - @Amount
            WHERE   AccountID = @AccountId
        END TRY
        BEGIN CATCH
            DECLARE @ErrorMessage  VARCHAR(4000)
                  , @ErrorSeverity INT
                  , @ErrorState    INT

            SELECT  @ErrorMessage  = ERROR_MESSAGE()
                  , @ErrorSeverity = ERROR_SEVERITY()
                  , @ErrorState    = ERROR_STATE()

            RAISERROR( @ErrorMessage, @ErrorSeverity, @ErrorState )
        END CATCH
    END

Now we added another complexity to the procedure because if we wrap RAISERROR in TRY CATCH block, we suppress the error and the front end left clueless as to what happened inside stored procedure. Therefore we need to "rethrow" it by catching the information about it and doing another RAISERROR. If we choose to do the same with THROW, it is a whole lot simpler. Just look at the next example.

 
CREATE PROCEDURE dbo.WithdrawMoney_THROW
    @AccountId INT
  , @Amount    DECIMAL(18, 2)
AS
    BEGIN
        BEGIN TRY
            IF EXISTS
                (
                    SELECT  1
                    FROM    dbo.Accounts
                    WHERE   AccountID              = @AccountId
                            AND (Amount - @Amount) > 0
                )
                THROW 51000, 'Insufficient Amount in account to perform withdrawal', 1


            UPDATE  dbo.Accounts
            SET Amount = Amount - @Amount
            WHERE   AccountID = @AccountId
        END TRY
        BEGIN CATCH
            THROW
        END CATCH
    END

In this case, we simply add THROW to CATCH block which acts as "RETHROW." Unfortunately for me, I write most of the code for SQL Server 2008 R2 which did not have THROW command. It is only available starting with SQL Server 2012. Therefore I have to use RAISERROR and always remember to wrap it inside TRY CATCH block to ensure that it always stops execution when I want it to do so.

Be sure to leave a comment and thank you so much for reading.