Error Handling In SQL Server Stored Procedures

Error Handling In SQL Server Stored Procedures

Handling errors effectively in SQL Server stored procedures is crucial for creating robust and reliable database applications. Proper error handling helps in debugging, maintaining, and ensuring the application behaves correctly during unexpected situations. This blog post will explore various techniques for handling errors in SQL Server stored procedures, providing code examples with clear explanations and outputs.

1. Basic Error Handling with TRY…CATCH

SQL Server introduced TRY…CATCH in SQL Server 2005 to handle errors more gracefully. The TRY…CATCH construct allows you to catch errors in a TRY block and handle them in a CATCH block.

Here’s a basic example:

CREATE PROCEDURE dbo.BasicErrorHandling
AS
BEGIN
    BEGIN TRY
        -- Attempt to divide by zero
        SELECT 1 / 0 AS Result;
    END TRY
    BEGIN CATCH
        -- Handle the error
        SELECT 
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_LINE() AS ErrorLine,
            ERROR_PROCEDURE() AS ErrorProcedure;
    END CATCH
END

Explanation:

• BEGIN TRY…END TRY: This block contains the code that might generate an error.
• BEGIN CATCH…END CATCH: This block contains the code to handle the error.
• ERROR_NUMBER(), ERROR_MESSAGE(), etc., are system functions that return detailed information about the error.

Output:

When you execute this stored procedure, it will catch the division by zero error and return:

ErrorNumber    ErrorMessage               ErrorSeverity    ErrorState    ErrorLine    ErrorProcedure
-----------    -------------------------  --------------   -----------   ---------    --------------
8134           Divide by zero error       16               1             4            BasicErrorHandling

2. Advanced Error Handling with Transaction Management

In complex scenarios, you may need to manage transactions to ensure data integrity. This involves rolling back changes if an error occurs.

CREATE TABLE Names
( 
Name1 VARCHAR(100),
Name2 VARCHAR(100)
) 

CREATE OR ALTER PROCEDURE dbo.AdvancedErrorHandling
AS
BEGIN
    BEGIN TRY
		BEGIN TRANSACTION;

        -- Perform some database operations


		INSERT INTO Names
		  (Name1, Name2)
		VALUES
		  ('Matt', 'Matthew'),
		  ('Matt', 'Marshal'),
		  ('Matt', 'Mattison')
		
        
		-- Simulate an error
        SELECT 1 / 0 AS Result;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Rollback the transaction if an error occurs
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        -- Handle the error
        SELECT 
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_LINE() AS ErrorLine,
            ERROR_PROCEDURE() AS ErrorProcedure;
    END CATCH
END

Explanation:

• BEGIN TRANSACTION: Starts a new transaction.
• COMMIT TRANSACTION: Commits the transaction if no errors occur.
• ROLLBACK TRANSACTION: Rolls back the transaction if an error occurs.
• The @@TRANCOUNT system function returns the number of active transactions.

Output:

When this stored procedure is executed, it will insert a row into Names table but then encounter a division by zero error and roll back the transaction, ensuring no changes are committed. Therefor you will not see any entries in the Names table.

ErrorNumber    ErrorMessage               ErrorSeverity    ErrorState    ErrorLine    ErrorProcedure
-----------    -------------------------  --------------   -----------   ---------    --------------
8134           Divide by zero error       16               1             11           AdvancedErrorHandling

3. Nested Error Handling

Sometimes, you might have nested stored procedures. You need to ensure errors are propagated correctly and handled at each level.

-- Inner stored procedure
CREATE PROCEDURE dbo.InnerProcedure
AS
BEGIN
    BEGIN TRY
        -- Simulate an error
        SELECT 1 / 0 AS Result;
    END TRY
    BEGIN CATCH
        -- Handle the error and rethrow it
        THROW;
    END CATCH
END

-- Outer stored procedure
CREATE PROCEDURE dbo.OuterProcedure
AS
BEGIN
    BEGIN TRY
        -- Call the inner stored procedure
        EXEC dbo.InnerProcedure;
    END TRY
    BEGIN CATCH
        -- Handle the error
        SELECT 
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_LINE() AS ErrorLine,
            ERROR_PROCEDURE() AS ErrorProcedure;
    END CATCH
END

Explanation:

• The InnerProcedure catches the error and rethrows it using the THROW statement.
• The OuterProcedure calls the InnerProcedure and handles the rethrown error.

Output:

When the OuterProcedure is executed, it will call the InnerProcedure, which will encounter a division by zero error and rethrow it. The OuterProcedure will catch and handle the error:

ErrorNumber    ErrorMessage               ErrorSeverity    ErrorState    ErrorLine    ErrorProcedure
-----------    -------------------------  --------------   -----------   ---------    --------------
8134           Divide by zero error       16               1             5            InnerProcedure

Conclusion

Effective error handling in SQL Server stored procedures involves using the TRY…CATCH construct to gracefully catch and handle errors, managing transactions to maintain data integrity, and ensuring errors are correctly propagated in nested procedures. By implementing these techniques, you can create robust and reliable database applications.