Detecting and Reporting Errors in Stored Procedures

Detecting and Reporting Errors in Stored Procedures - Part 1: SQL Server 2000

Introduction

Detailed error reporting from stored procedures can be extremely valuable when debugging problems in your application. I will share different levels of detail that you can build into your stored procedures. You can choose the level of detail appropriate for your situation.

Let's get right to the examples. (Note that all examples use Northwind tables and that we're focusing here on errors, so all tests are for failure cases.)

Compatibility: All code works on both SQL Server 2000 and SQL Server 2005 Beta 2. It does not leverage any SQL Server 2005 features.

Level Zero - No Detection, No Reporting

CREATE PROCEDURE dbo.CreateRegion0 (
    @RegionID           int,
    @RegionDescription nchar(50)
) AS BEGIN
    SET NOCOUNT ON

    INSERT INTO dbo.Region (
        RegionID,
        RegionDescription
    )
    VALUES (
        @RegionID,
        @RegionDescription
    )
END
GO

Procedure CreateRegion0 includes no error detection or reporting. If we execute this with a RegionID that already exists,

DECLARE @rtnVal int
EXEC @rtnVal = dbo.CreateRegion0
@RegionID = 2,
@RegionDescription = N'Western'

we get this error

Server: Msg 2627, Level 14, State 1, Procedure SPTest_CreateRegion0, Line 9
Violation of PRIMARY KEY constraint 'PK_Region'. Cannot insert duplicate key in object 'Region'.
The statement has been terminated.

and @rtnVal is -4. The error from SQL Server is quite helpful but does not give any details.

Level One - Error Detection with Minimal Reporting

CREATE PROCEDURE dbo.CreateRegion1 (
    @RegionID           int,
    @RegionDescription nchar(50)
) AS BEGIN
    SET NOCOUNT ON

    DECLARE @error     int
    DECLARE @rowcount int

    INSERT INTO dbo.Region (
        RegionID,
        RegionDescription
    )
    VALUES (
        @RegionID,
        @RegionDescription
    )

    SELECT
        @error     = @@error,
        @rowcount = @@rowcount

    IF @error <> 0 BEGIN
        RAISERROR('Error %d inserting into Region', 10, 1, @error)
        RETURN(@error)
    END
    ELSE IF @rowcount <> 1 BEGIN
        RAISERROR('Rowcount %d inserting into Region', 10, 1, @rowcount)
                WITH SETERROR
        RETURN(@@error)
    END

    RETURN(0)
END
GO

Procedure CreateRegion1 includes error detection and minimal reporting through in-line RAISERROR calls. If we execute this with a RegionID that already exists,

DECLARE @rtnVal int
EXEC @rtnVal = dbo.CreateRegion1
@RegionID = 2,
@RegionDescription = N'Western'

we get this error

Server: Msg 2627, Level 14, State 1, Procedure SPTest_CreateRegion1, Line 12
Violation of PRIMARY KEY constraint 'PK_Region'. Cannot insert duplicate key in object 'Region'.
The statement has been terminated.
Error 2627 inserting into Region

and @rtnVal is 2627. Here, there are two error messages, but the "custom" message doesn't give us much more information. However, the return value is much more useful than -4.

Some would argue that the @@rowcount check adds no value. I disagree, especially in cases where you use "INSERT INTO ... SELECT ... FROM ...". If you expect to insert exactly one row and the SELECT returns some number of rows other than one, the caller needs to know that the insert did not happen. Could you leave the @@rowcount check out sometimes? Sure. I never do.

Level Two - User-Defined Messages, More-Detailed Reporting

sp_addmessage 2000000001, 10, N'Error in %s: Error %d inserting into %s. %s', US_ENGLISH, FALSE, REPLACE
GO
sp_addmessage 2000000002, 10, N'Error in %s: Insert into %s returned %d rows. %s', US_ENGLISH, FALSE, REPLACE
GO

CREATE PROCEDURE dbo.CreateRegion2 (
    @RegionID           int,
    @RegionDescription nchar(50)
) AS BEGIN
    -- Error detection and reporting
    SET NOCOUNT ON

    DECLARE @error     int
    DECLARE @errorMsg nvarchar(512)
    DECLARE @rowcount int
    DECLARE @spName    sysname

    SELECT @spName = Object_Name(@@ProcID)

    INSERT INTO dbo.Region (
        RegionID,
        RegionDescription
    )
    VALUES (
        @RegionID,
        @RegionDescription
    )

    SELECT
        @error     = @@error,
        @rowcount = @@rowcount

    IF @error <> 0 BEGIN
        EXEC dbo.TagValueList @errorMsg OUTPUT, N'RegionID', @RegionID,
                N'RegionDescription', @RegionDescription
        RAISERROR(2000000001, 10, 1, @spName, @error, 'Region', @errorMsg)
        RETURN(@error)
    END
    ELSE IF @rowcount <> 1 BEGIN
        EXEC dbo.TagValueList @errorMsg OUTPUT, N'RegionID', @RegionID,
                N'RegionDescription', @RegionDescription
        RAISERROR(2000000002, 10, 1, @spName, 'Region', @rowcount, @errorMsg)
                WITH SETERROR
        RETURN(@@error)
    END

    RETURN(0)
END
GO

Procedure CreateRegion2 uses user-defined messages and more-detailed reporting.

This procedure uses a special utility procedure (TagValueList) that creates a tag-value list for the error message. This list can be used to give the details that will help you debug the problem when it occurs. From the example code, a call to this procedure creates this string:

(RegionID: 2) (RegionDescription: Western)

This allows you to log exactly what failed, not just that a failure happened. Another important feature of this procedure is the ability to see which parameters or local variables are NULL. In the second test of CreateRegion2, @RegionDescription is set to NULL. This is the tag-value string returned:

(RegionID: 2) (RegionDescription: NULL)

After calling CreateRegion2,

DECLARE @rtnVal int
EXEC @rtnVal = dbo.CreateRegion2
@RegionID = 2,
@RegionDescription = N'Western'

we get this error:

Server: Msg 2627, Level 14, State 1, Procedure CreateRegion2, Line 16
Violation of PRIMARY KEY constraint 'PK_Region'. Cannot insert duplicate key in object 'Region'.
The statement has been terminated.
Error in CreateRegion2: Error 2627 inserting into Region. (RegionID: 2) (RegionDescription: Western)

and @rtnVal is 2627. Using a different test (where @RegionDescription is set to NULL),

DECLARE @rtnVal int
EXEC @rtnVal = dbo.CreateRegion2
@RegionID = 2,
@RegionDescription = NULL

we get this error:

Server: Msg 515, Level 16, State 2, Procedure CreateRegion2, Line 16
Cannot insert the value NULL into column 'RegionDescription', table 'Northwind.dbo.Region'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Error in CreateRegion2: Error 515 inserting into Region. (RegionID: 2) (RegionDescription: NULL)

and @rtnVal is 515.

Here, the results are better than in level one because of the stored procedure name and tag-value pair details in the RAISERROR output.

Level Three - User-Defined Messages, Failure Reasons

sp_addmessage 2000000003, 10, N'Error in %s: Failed creating Region record because this RegionID already exists. %s', US_ENGLISH, FALSE, REPLACE
GO

CREATE PROCEDURE CreateRegion3 (
    @RegionID           int,
    @RegionDescription nchar(50)
) AS BEGIN
    -- Full error detection and reporting
    SET NOCOUNT ON

    DECLARE @error     int
    DECLARE @errorMsg nvarchar(512)
    DECLARE @rowcount int
    DECLARE @spName    sysname

    SELECT @spName = Object_Name(@@ProcID)

    INSERT INTO dbo.Region (
        RegionID,
        RegionDescription
    )
    VALUES (
        @RegionID,
        @RegionDescription
    )

    SELECT
        @error    = @@error,
        @rowcount = @@rowcount

    IF @error <> 0 BEGIN
        EXEC dbo.TagValueList @errorMsg OUTPUT, N'RegionID', @RegionID,
                N'RegionDescription', @RegionDescription

        -- Error 2627 indicates failure of a primary key constraint
        IF @error = 2627
          AND EXISTS (
            SELECT 1
            FROM dbo.Region
            WHERE RegionID = @RegionID
        ) BEGIN
            RAISERROR(2000000003, 10, 1, @spName, @errorMsg) WITH SETERROR
            RETURN(@@error)
        END
        ELSE BEGIN
            RAISERROR(2000000001, 10, 1, @spName, @error, 'Region', @errorMsg)
            RETURN(@error)
        END
    END
    ELSE IF @rowcount <> 1 BEGIN
        EXEC dbo.TagValueList @errorMsg OUTPUT, N'RegionID', @RegionID,
                N'RegionDescription', @RegionDescription
        RAISERROR(2000000002, 10, 1, @spName, 'Region', @rowcount, @errorMsg)
                WITH SETERROR
        RETURN(@@error)
    END

    RETURN(0)
END
GO

Procedure CreateRegion3 includes user-defined messages and very detailed reporting plus the ability to report on specific reasons for failure. Instead of raising an error stating that an error occurred inserting into Region, this procedure checks for a specific error that is expected (primary key failure). If that is the error, then it raises an error with details about that error. If that is not the error, then it raises a more general error but still supplies as much detail as possible.

After calling CreateRegion3, we get this error:

Server: Msg 2627, Level 14, State 1, Procedure CreateRegion3, Line 16
Violation of PRIMARY KEY constraint 'PK_Region'. Cannot insert duplicate key in object 'Region'.
The statement has been terminated.
Error in CreateRegion3: Failed creating Region record because this RegionID already exists. (RegionID: 2) (RegionDescription: Western)

and @rtnVal is 2000000003. Here, the results are better than in level two because the RAISERROR output gives details about which error occurred.

The time when this is most valuable is when a table has multiple constraints. For instance, if an insert into Orders failed, it could be because of an invalid CustomerID, EmployeeID, or ShipperID (ShipVia). All three of these would cause an @@error value of 547. Now look at ErrorReportingOrders. This script includes a level-three implementation of CreateOrder. Here, if @@error is 547 (foreign key violation), then each possible cause is tested. If none of those cases is true, then a more generic error is raised.

Code that calls CreateOrder can include this type of logic:

If <returnValue> = 2000000010, the CustomerID was invalid
Else if <returnValue> = 2000000011, the EmployeeID was invalid
Else if <returnValue> = 2000000012, the ShipperID was invalid
Else if <returnValue> = 2000000001, an error occurred on INSERT
Else if <returnValue> = 2000000002, the INSERT failed and rowcount was not 1
Else unexpected error

In many cases, the calling code does not need such detailed logic. But when it is needed, this allows very detailed information to be passed to the calling code.

Conclusion

You won't choose to use level three code in every case, but when you need it, it will help you to quickly find the source of the problem. I recommend that you use either level two or three. It may take more coding up-front, but when something goes wrong in production, you will be very glad that you took the time during development to include full error detection and reporting.

Watch for "Detecting and Reporting Errors in Stored Procedures - Part 2: SQL Server 2005 TRY-CATCH Blocks".

Appendix

TagValueList Utility Procedure

This is a utility procedure for use in error reporting.

CREATE PROCEDURE dbo.TagValueList (
    @List    varchar(512)                 OUTPUT,
    @Tag1    varchar(64) = NULL,
    @Value1 varchar(64) = NULL,
    @Tag2    varchar(64) = NULL,
    @Value2 varchar(64) = NULL,
    @Tag3    varchar(64) = NULL,
    @Value3 varchar(64) = NULL,
    @Tag4    varchar(64) = NULL,
    @Value4 varchar(64) = NULL,
    @Tag5    varchar(64) = NULL,
    @Value5 varchar(64) = NULL,
    @Tag6    varchar(64) = NULL,
    @Value6 varchar(64) = NULL,
    @Tag7    varchar(64) = NULL,
    @Value7 varchar(64) = NULL,
    @Tag8    varchar(64) = NULL,
    @Value8 varchar(64) = NULL,
    @Tag9    varchar(64) = NULL,
    @Value9 varchar(64) = NULL,
    @Tag10   varchar(64) = NULL,
    @Value10 varchar(64) = NULL,
    @Tag11   varchar(64) = NULL,
    @Value11 varchar(64) = NULL,
    @Tag12   varchar(64) = NULL,
    @Value12 varchar(64) = NULL,
    @Tag13   varchar(64) = NULL,
    @Value13 varchar(64) = NULL,
    @Tag14   varchar(64) = NULL,
    @Value14 varchar(64) = NULL,
    @Tag15   varchar(64) = NULL,
    @Value15 varchar(64) = NULL,
    @Tag16   varchar(64) = NULL,
    @Value16 varchar(64) = NULL,
    @Tag17   varchar(64) = NULL,
    @Value17 varchar(64) = NULL,
    @Tag18   varchar(64) = NULL,
    @Value18 varchar(64) = NULL,
    @Tag19   varchar(64) = NULL,
    @Value19 varchar(64) = NULL,
    @Tag20   varchar(64) = NULL,
    @Value20 varchar(64) = NULL
) AS BEGIN
    -- Called to build up a tag/value pair string. Normally used for error messages.
    -- Pass in as many Tag/Value pairs as you want to see in @List (up to twenty pairs).

    -- Tag/Value 1
    IF @Tag1 IS NULL BEGIN
        RETURN(0)
    END
    -- This line begins the string
    SELECT @List = '(' + @Tag1 + ': ' + ISNULL(RTRIM(@Value1), 'NULL') + ')'

    -- Tag/Value 2
    IF @Tag2 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag2 + ': ' + ISNULL(RTRIM(@Value2), 'NULL') + ')'

    -- Tag/Value 3
    IF @Tag3 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag3 + ': ' + ISNULL(RTRIM(@Value3), 'NULL') + ')'

    -- Tag/Value 4
    IF @Tag4 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag4 + ': ' + ISNULL(RTRIM(@Value4), 'NULL') + ')'

    -- Tag/Value 5
    IF @Tag5 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag5 + ': ' + ISNULL(RTRIM(@Value5), 'NULL') + ')'

    -- Tag/Value 6
    IF @Tag6 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag6 + ': ' + ISNULL(RTRIM(@Value6), 'NULL') + ')'

    -- Tag/Value 7
    IF @Tag7 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag7 + ': ' + ISNULL(RTRIM(@Value7), 'NULL') + ')'

    -- Tag/Value 8
    IF @Tag8 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag8 + ': ' + ISNULL(RTRIM(@Value8), 'NULL') + ')'

    -- Tag/Value 9
    IF @Tag9 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag9 + ': ' + ISNULL(RTRIM(@Value9), 'NULL') + ')'

    -- Tag/Value 10
    IF @Tag10 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag10 + ': ' + ISNULL(RTRIM(@Value10), 'NULL') + ')'

    -- Tag/Value 11
    IF @Tag11 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag11 + ': ' + ISNULL(RTRIM(@Value11), 'NULL') + ')'

    -- Tag/Value 12
    IF @Tag12 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag12 + ': ' + ISNULL(RTRIM(@Value12), 'NULL') + ')'

    -- Tag/Value 13
    IF @Tag13 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag13 + ': ' + ISNULL(RTRIM(@Value13), 'NULL') + ')'

    -- Tag/Value 14
    IF @Tag14 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag14 + ': ' + ISNULL(RTRIM(@Value14), 'NULL') + ')'

    -- Tag/Value 15
    IF @Tag15 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag15 + ': ' + ISNULL(RTRIM(@Value15), 'NULL') + ')'

    -- Tag/Value 16
    IF @Tag16 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag16 + ': ' + ISNULL(RTRIM(@Value16), 'NULL') + ')'

    -- Tag/Value 17
    IF @Tag17 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag17 + ': ' + ISNULL(RTRIM(@Value17), 'NULL') + ')'

    -- Tag/Value 18
    IF @Tag18 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag18 + ': ' + ISNULL(RTRIM(@Value18), 'NULL') + ')'

    -- Tag/Value 19
    IF @Tag19 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag19 + ': ' + ISNULL(RTRIM(@Value19), 'NULL') + ')'

    -- Tag/Value 20
    IF @Tag20 IS NULL BEGIN
        RETURN(0)
    END
    SELECT @List = @List + ' (' + @Tag20 + ': ' + ISNULL(RTRIM(@Value20), 'NULL') + ')'

    RETURN(0)
END
GO

CreateOrder Procedure and Tests

sp_addmessage 2000000001, 10, N'Error in %s: Error %d inserting into %s. %s', US_ENGLISH, FALSE, REPLACE
GO
sp_addmessage 2000000002, 10, N'Error in %s: Insert into %s returned %d rows. %s', US_ENGLISH, FALSE, REPLACE
GO
sp_addmessage 2000000010, 10, N'Error in %s: Failed creating Orders record because the CustomerID supplied is not valid. %s', US_ENGLISH, FALSE, REPLACE
GO
sp_addmessage 2000000011, 10, N'Error in %s: Failed creating Orders record because the EmployeeID supplied is not valid. %s', US_ENGLISH, FALSE, REPLACE
GO
sp_addmessage 2000000012, 10, N'Error in %s: Failed creating Orders record because the ShipperID supplied is not valid. %s', US_ENGLISH, FALSE, REPLACE
GO

CREATE PROCEDURE dbo.CreateOrder (
    @CustomerID nchar(5),
    @EmployeeID int,
    @ShipperID int,
    @OrderID    int         OUTPUT
) AS BEGIN
    -- Full error detection and reporting (multiple FKs)
    SET NOCOUNT ON

    DECLARE @error    int
    DECLARE @errorMsg nvarchar(512)
    DECLARE @rowcount int
    DECLARE @spName   sysname

    SELECT @spName = Object_Name(@@ProcID)

    INSERT INTO dbo.Orders (
        CustomerID,
        EmployeeID,
        ShipVia,
        OrderDate
    )
    VALUES (
        @CustomerID,
        @EmployeeID,
        @ShipperID,
        GETDATE()
    )

    SELECT
        @error    = @@error,
        @rowcount = @@rowcount,
        @OrderID = SCOPE_IDENTITY()

    IF @error <> 0 BEGIN
        EXEC TagValueList @errorMsg OUTPUT, N'CustomerID', @CustomerID,
            N'EmployeeID', @EmployeeID, N'ShipperID', @ShipperID

        -- Error 547 indicates failure of a foreign key constraint
        IF @error = 547 BEGIN
            IF NOT EXISTS (
                SELECT 1
                FROM Customers
                WHERE CustomerID = @CustomerID
            ) BEGIN
                RAISERROR(2000000010, 10, 1, @spName, @errorMsg) WITH SETERROR
                RETURN(@@error)
            END
            ELSE IF NOT EXISTS (
                SELECT 1
                FROM Employees
                WHERE EmployeeID = @EmployeeID
            ) BEGIN
                RAISERROR(2000000011, 10, 1, @spName, @errorMsg) WITH SETERROR
                RETURN(@@error)
            END
            ELSE IF NOT EXISTS (
                SELECT 1
                FROM Shippers
                WHERE ShipperID = @ShipperID
            ) BEGIN
                RAISERROR(2000000012, 10, 1, @spName, @errorMsg) WITH SETERROR
                RETURN(@@error)
            END
        END
        ELSE BEGIN
            RAISERROR(2000000001, 10, 1, @error, @spName, 'Region', @errorMsg)
            RETURN(@error)
        END
    END
    ELSE IF @rowcount <> 1 BEGIN
        EXEC TagValueList @errorMsg OUTPUT, N'CustomerID', @CustomerID,
            N'EmployeeID', @EmployeeID, N'ShipperID', @ShipperID
        RAISERROR(2000000002, 10, 1, @spName, 'Orders', @rowcount, @errorMsg) WITH SETERROR
        RETURN(@@error)
    END

    RETURN(0)
END
GO

-- ===============================================================================================
-- Tests
-- ===============================================================================================
DECLARE @rtnVal int
DECLARE @rtnMsg varchar(255)
DECLARE @msg    varchar(255)
DECLARE @order1 int

PRINT ''
PRINT 'EXEC CreateOrder (invalid CustomerID)'
EXEC @rtnVal = dbo.CreateOrder
    @CustomerID = N'xxx',
    @EmployeeID = 1,
    @ShipperID = 2,
    @OrderID    = @order1     OUTPUT

SELECT @rtnMsg = 'Return value: ' + CONVERT(varchar, @rtnVal)
PRINT @rtnMsg

PRINT ''
PRINT 'EXEC CreateOrder (invalid EmployeeID)'
EXEC @rtnVal = dbo.CreateOrder
    @CustomerID = N'BOTTM',
    @EmployeeID = -1,
    @ShipperID = 2,
    @OrderID    = @order1     OUTPUT

SELECT @rtnMsg = 'Return value: ' + CONVERT(varchar, @rtnVal)
PRINT @rtnMsg

PRINT ''
PRINT 'EXEC CreateOrder (invalid ShipperID)'
EXEC @rtnVal = dbo.CreateOrder
    @CustomerID = N'BOTTM',
    @EmployeeID = 1,
    @ShipperID = -1,
    @OrderID    = @order1     OUTPUT

SELECT @rtnMsg = 'Return value: ' + CONVERT(varchar, @rtnVal)
PRINT @rtnMsg

PRINT ''
PRINT 'EXEC CreateOrder'
EXEC @rtnVal = dbo.CreateOrder
    @CustomerID = N'BOTTM',
    @EmployeeID = 1,
    @ShipperID = 1,
    @OrderID    = @order1     OUTPUT

SELECT @rtnMsg = 'Return value: ' + CONVERT(varchar, @rtnVal)
PRINT @rtnMsg
IF @rtnVal = 0 BEGIN
    SELECT @msg = 'Successfully created order ' + CONVERT(varchar, @order1)
    PRINT @msg
END
GO

Originally published December 15, 2004 on SqlJunkies.com