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
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
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
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
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
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
Be sure to leave a comment and thank you so much for reading.
