Error handling problem in SQL Server

Question

I'm using C# and a typed dataset which is linked to stored procedure in SQL Server Express database. In the stored procedure I'm using try catch for error handling.

Here is my catch block:

begin catch
    select 
        error_number() as errorNumber, 
        error_severity() as errorSeverity,
        error_state() as errorState,
        error_procedure() as errorProcedure,
        error_line() as errorLine,
        error_message() as errorMessage
end catch

1. is try catch the best way to handle errors in db??

2. since SQL Server will return the error info as a table, how do I recognize if the returned table is full of data or it contains error info??

Edit: ok so i get the exception and i rethrow it?? but why? i mean if i don't catch it at the first place it will bubble up to my application and i can manage it there. so why should i use try catch??

Answer 1

Just remember that if you need to report the exception to your application (as in catch (SQLException ex) in your C#), your SPROC catch block will need to rethrow (RAISERROR) the exception, and also ensure that the severity level is > 10 TRY and RAISERROR in T-SQL

Answer 2

This can be a good way to handle errors in a stored procedure, however more often than not you would do that to log the error to a table:

begin catch
    insert into ErrorLog
    select 
        error_number() as errorNumber, 
        error_severity() as errorSeverity,
        error_state() as errorState,
        error_procedure() as errorProcedure,
        error_line() as errorLine,
        error_message() as errorMessage
end catch

However, based on the fact that exceptions should only happen in exceptional circumstances, you almost certainly want to rethrow the error so that your code can handle this exception in the most rational way.

In sql server this is achieved using the RAISERROR (docs) command.

Your code would end up looking someting like:

BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

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

    -- You could log the error here
    INSERT INTO ErrorLog(message, severity, state)
    VALUES(@ErrorMessage, @ErrorSeverity, @ErrorState)

    -- Use RAISERROR inside the CATCH block to return error
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

Answer 3

Also be aware that if your transaction is rolled back, your log records are as well - ending up with no log entries...