Intro

One of the best articles I've seen on this topic is Exception Handling in Sql Server [http://sqlhints.com/2014/01/20/exception-handling-in-sql-server/] by Basavaraj Biradar. This page is based on that article plus some of my own findings.

See also:

Error Message parts

An error raised by the Microsoft SQL Server Database Engine has roughly six parts, which correspond to six built in functions, and to six proprties of the SqlException class of the .Net Framework. Some also correspond to columns in the sys.messsages table.

  1. Error number.
    • ERROR_NUMBER().
    • System.Data.SqlClient.SqlException.Number
    • sys.messages.message_id.
    • A unique error number. <= 50,000 are system-defined. > 50,000 are user-defined in the sys.messages table, which in turn is managed with the sp_addmessage and sp_dropmessage stored procedures.
  2. Error message string.
    • ERROR_MESSAGE().
    • System.Data.SqlClient.SqlException.Message
    • sys.messages.text.
    • Diagnostic info about the cause of the error.
  3. Severity level.
    • ERROR_SEVERITY().
    • System.Data.SqlClient.SqlException.Class
    • sys.messages.severity.
    • Low severity (0-10) are informational or warnings, and do NOT pass to a CATCH block.
      11-16 are programming errors. 17-25 are resource, hardware, OS, or SQL Server internal errors.
      19-25 can only be raised by users with SysAdmin rights.
      20-25 terminate the connection.
  4. State.
    • ERROR_STATE().
    • System.Data.SqlClient.SqlException.State
    • sys.messages N/A.
    • If a user-defined error is raised at different lines, then a different state (0-255) can be passed.
  5. Procedure name.
    • ERROR_PROCEDURE().
    • System.Data.SqlClient.SqlException.Procedure
    • sys.messages. N/A.
    • The name of the stored procedure or trigger in which the error occurred.
  6. Line number.
    • ERROR_LINE().
    • System.Data.SqlClient.SqlException.LineNumber
    • sys.messages. N/A.
    • Which statement in a batch, stored procedure, trigger, or function generated the error.

Here is a typical error message:

Msg 208, Level 16, State 1, Procedure MySproc, Line 4
Invalid object name 'NonExistingTable'

SET XACT_ABORT

The XACT_ABORT statement specifies whether SQL Server automatically rolls back the current transaction when a T-SQL statement raises a run-time error.

If BEGIN TRANSACTION ... END TRANSACTION is NOT used, then each statement is treated like a micro-transaction. EG:

SET XACT_ABORT ON;
INSERT INTO Tbl(Col) VALUES (1); -- 1st succeeds.
INSERT INTO Tbl(Col) VALUES (2); -- 2nd fails.
INSERT INTO Tbl(Col) VALUES (3); -- 3rd succeeds.

If TRY ... CATCH is used, EG:

SET XACT_ABORT ON;
BEGIN TRY
    -- 1st succeeds:
    INSERT INTO Tbl(Col) VALUES (1);

    -- 2nd fails, making the 3rd never happen:
    INSERT INTO Tbl(Col) VALUES (2);

    -- 3rd could succeed, but never gets the chance:
    INSERT INTO Tbl(Col) VALUES (3);
END TRY
BEGIN CATCH
    PRINT 'In CATCH block';
END CATCH

If BEGIN TRANSACTION ... END TRANSACTION is used, then the block acts as transaction. EG:

SET XACT_ABORT ON;
BEGIN TRANSACTION
    -- 1st could succeed, but gets rolled back:
    INSERT INTO Tbl(Col) VALUES (1);

    -- 2nd fails, making the 1st and 2nd roll back:
    INSERT INTO Tbl(Col) VALUES (2);

    -- 3rd could succeed, but gets rolled back.
    INSERT INTO Tbl(Col) VALUES (3);
COMMIT TRANSACTION

RAISERROR v THROW

Both RAISERROR and THROW are used to generated an error message and begin processing the error. Note that there is only one E in RAISERROR!

RAISERROR syntax:

RAISERROR(
    {msg_id | msg_str | @local_variable}
    {, severity, state}
    [, argument1 [, ... n]]
) [WITH option1 [, ...n]]
[;]

Notes:

  • msg_id. 50,000-2,147,483,647. Default value is 50,000.
  • >msg_str | @local_varialbe. Formatting similar to printf. Error number is 50,000.
  • severity. 0-25. Specify -1 to return the associate severity.
  • state. 0-255. Specify -1 to return the associate state.
  • argument. Parameters passed to mst_str.
  • option. One of the following: LOG, NOWAIT, SETERROR.

EG

RAISERROR(15600, -1, -1, 'mysp_CreateCustomer');
-- Output like:
Msg 15600, Level 15, State 1, Line 1
An invalid parameter or option was specified for procedure 'mysp_CreateCustomer'.
RAISERROR('Error raised.', 16, 1)
-- Output like:
Msg 50000, Level 16, State 1, Line 1
Error raised.
DECLARE @StringVariable NVARCHAR(50);
SET @StringVariable = N'My message %7.3s!';
RAISERROR (@StringVariable, -- Message text.
           10, -- Severity,
           1, -- State,
           N'abcde'); -- First argument supplies the string.
-- Output like:
My message     abc!

THROW syntax:

THROW [
    {error_number | @local_variable},
    {message | @local_variable},
    {state | @local_variable} ]
[;]

Notes:

  • error_number. 50,000-2,147,483,647.
  • message. nvarchar(2048).
  • state. 0-255.

EG:

THROW 51000, 'Error raised', 1;
-- Output like:
Msg 50000, Level 16, State 1, Line 1
Error raised.
EXEC sys.sp_addmessage
    @msgnum   = 60000,
    @severity = 16,
    @msgtext  = N'A numeric parameter (%d), a string parameter (%s).',
    @lang = 'us_english';
GO
DECLARE @msg NVARCHAR(2048) = FORMATMESSAGE(60000, 500, N'My string');
THROW 60000, @msg, 1;
-- Output like:
Msg 60000, Level 16, State 1, Line 2
A numeric parameter (500), a string parameter (My string).

Key Differences

Some simple differences:

  • RAISERROR was introduced with SQL Server 7.0, while THROW was introduced with SQL Server 2012, and the latter is the preferred way.
  • RAISERROR allows you to set severity, but THROW defaults to 16 unless re-throwing in the CATCH block.
  • RAISERROR does NOT require the preceding statement to end with ;, but THROW does.
  • RAISERROR CAN raise system exeptions (< 50,000), while THROW CANNOT unless re-throwing in the CATCH block.
  • RAISERROR CANNOT raise system exeptions > 50,000 that are NOT in sys.messages, but THROW CAN.
  • RAISERROR CAN use printf messages, while THROW CAN but in slightly different way.

Re-throwing the exception works with THROW but not with RAISERROR. EG:

BEGIN TRY
    DECLARE @result INT;
    SET @result = 55/0; -- Divide by zero error
END TRY
BEGIN CATCH
    DECLARE
        @ErMessage NVARCHAR(2048),
        @ErSeverity INT,
        @ErState INT;
    SELECT
        @ErMessage = ERROR_MESSAGE(),
        @ErSeverity = ERROR_SEVERITY(),
        @ErState = ERROR_STATE();
    RAISERROR (@ErMessage,
        @ErSeverity,
        @ErState);
END CATCH
-- Output like:
Msg 50000, Level 16, State 1, Line 14
Divide by zero error encountered.
-- : Msg should be 8134! Line should be 4!
BEGIN TRY
    DECLARE @result INT
    SET @result = 55/0 -- Divide by zero error
END TRY
BEGIN CATCH
    DECLARE
        @ErMessage NVARCHAR(2048),
        @ErSeverity INT,
        @ErState INT;
    SELECT
        @ErMessage = ERROR_MESSAGE(),
        @ErSeverity = ERROR_SEVERITY(),
        @ErState = ERROR_STATE();
    THROW
END CATCH
-- Output like:
Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.

No statements after THROW are run, but the same is not true for RAISERROR. EG:

PRINT 'BEFORE RAISERROR';
RAISERROR('RAISERROR TEST', 16,1);
PRINT 'AFTER RAISERROR';
-- Output like:
BEFORE RAISERROR
Msg 50000, Level 16, State 1, Line 2
RAISERROR TEST
AFTER RAISERROR
PRINT 'BEFORE THROW';
THROW 50000, 'THROW TEST', 1;
PRINT 'AFTER THROW';
-- Output like:
BEFORE THROW
Msg 50000, Level 16, State 1, Line 2
THROW TEST

Error Handling Template

I am largely in agreement with the template by Basavaraj Biradar. However his template utilizes THROW (available in SQL Server 2012+), and I have to deal with SQL Server 2008. So I will provide a template for RAISERROR. I am also putting in basic logging in my template, and a broad way to return error info back to the app.

SET NOCOUNT OFF;
SET XACT_ABORT ON;
BEGIN TRY
    DECLARE @Success BIT;
    SET @Success = 1;
    DECLARE @Message NVARCHAR(2048);
    SET @Message = ''; -- Usually blank if Success=1.


    -- Code which doesn't require a transaction.

    BEGIN TRANSACTION
        -- All or nothing transactional statements.
    COMMIT TRANSACTION;

    -- If sproc modifies the database
    -- (with a DROP, CREATE, DELETE, UPDATE, INSERT, etc.),
    -- then return results with a SELECT.
    -- Others use a return code (One int only) or output parameters,
    -- but this way the app gets a SELECT regardless of success.
    SELECT @Success AS Success, @Message AS msg;
        -- Extra fields can be added as needed:
        -- @@IDENTITY AS InsertedID -- If sproc was an insert.
        -- @@ROWCOUNT AS RowsAffected

END TRY
BEGIN CATCH
    -- Next block only needed if transaction used.
    IF @@TRANCOUNT > 0 AND XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    -- Do error logging if desired:
    DECLARE @Error_Number INT,
        @Error_Message VARCHAR(4000),
        @Error_Severity INT,
        @Error_State INT,
        @Error_Procedure VARCHAR(200),
        @Error_Line INT,
        @UserName VARCHAR(200),
        @HostName VARCHAR(200),
        @Time_Stamp DATETIME;
    SELECT @Error_Number = ISNULL(ERROR_NUMBER(), 0),
        @Error_Message = ISNULL(ERROR_MESSAGE(), 'NULL Message'),
        @Error_Severity = ISNULL(ERROR_SEVERITY(), 0),
        @Error_State = ISNULL(ERROR_STATE(), 1),
        @Error_Line = ISNULL(ERROR_LINE(), 0),
        @Error_Procedure = ISNULL(ERROR_PROCEDURE(), ''),
        @UserName = SUSER_SNAME(),
        @HostName = HOST_NAME(),
        @Time_Stamp = GETDATE();
    -- Use a custom table error logging.
    INSERT INTO ErrorLog(Error_Number,
        Error_Message,
        Error_Severity,
        Error_State,
        Error_Line,
        Error_Procedure,
        UserName,
        HostName,
        Time_Stamp)
    SELECT @Error_Number,
        @Error_Message,
        @Error_Severity,
        @Error_State,
        @Error_Line,
        @Error_Procedure,
        @UserName,
        @HostName,
        @Time_Stamp;

    -- Do corrective actions as desired.

    -- Return error info:
    SET @Success = 0;
    SET @Message = CAST(@Error_Number AS VARCHAR(9)) + ': ' + @Error_Message;
    SELECT @Success AS success, @Message AS msg;

    -- Re-throw only really works in SQL Server 2012+.
    THROW
END CATCH;

Links

Links that lead to off-site pages about SQL Server Error Handling.



GeorgeHernandez.comSome rights reserved