SQL Server 2008 R2 – Stored Procedure Returns -4 After Catching Exception

sql serversql-server-2008-r2stored-procedures

In a stored procedure, I am finding that if it successfully inserts a record, it returns 0. If it fails on a primary key violation (which is caught and ignored), it returns -4. Where is the -4 coming from?

Here is the stored procedure:

ALTER PROCEDURE [dbo].[pSetUserItemLike] 
        @UserID int
        ,@ItemID int
AS
BEGIN
    SET NOCOUNT ON;

    -- for exception handling
    DECLARE @ErrorMessage nvarchar(max)
    DECLARE @ErrorSeverity int
    DECLARE @ErrorState int

    BEGIN TRY
        INSERT dbo.UserItemLikes
        (
            UserID
            ,ItemID
        )
        VALUES
        (
            @UserID
            ,@ItemID
        )
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() <> 2627 -- IGNORE PRIMARY KEY VIOLATION
            SELECT 
                @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5))
                , @ErrorSeverity = ERROR_SEVERITY()
                , @ErrorState = ERROR_STATE();
            RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); 
    END CATCH
END

Note: I am not actually using the return value, I just noticed it as SSMS' "Execute Procedure" automatically captures the return value and puts it in a variable, and couldn't figure out why this was happening.

Best Answer

While I agree that you're raising an error even if you have a PK violation (as the other answer suggests, you need BEGIN/END, because your IF conditional currently assumes that everything indented after it will be part of the IF logic), that doesn't really answer why you're getting -4 specifically.

You don't have an explicit return value in the event that you catch an error. I'll borrow from Erland Sommarskog's answer here, which states:

But if there is no RETURN statement, but an error occurs during execution, the return value is 10 minus the severity level of the error. Division by zero is level 16, thus the return value is -6. Permissions errors are typical level 14, thus the return value is -4.

As you may guess this is not terribly useful, but this: 0 is success, and everything else is an error.

So, raise a PK error, you get:

Msg 2627, Level 14, State 1

According to Erland's formula:

10 - 14 = -4

You don't get the actual level in @ErrorLevel when there's a PK violation because those local variables aren't populated when the error message is 2627 (your IF condition prevents that for that specific scenario, but doesn't prevent the RAISERROR from happening). If you manage to say RAISERROR(NULL,NULL,NULL); then Erland's rule applies; this is because, if an actual error has happened (I fake this in a demo below), SQL Server will replace that middle param with the actual ERROR_LEVEL() from the exception. I can show that this is true for other error levels other than 14; for example, the following will print out Hi/-6 after I fail to assign the variable for a divide by zero error, which is severity level 16:

CREATE PROCEDURE dbo.Test
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @ErrorLevel INT;

  BEGIN TRY
    SELECT 1/0; -- level 16!
  END TRY
  BEGIN CATCH
    IF ERROR_NUMBER() <> 8134 -- divide by 0
      SET @ErrorLevel = ERROR_SEVERITY();
    RAISERROR('Hi', @ErrorLevel, 1);
  END CATCH
END
GO

DECLARE @rc INT;
EXEC @rc = dbo.Test;
PRINT @rc;

(In fact, inside a CATCH block, I couldn't find a way to override the actual exception's true severity level, even by hard-coding @ErrorLevel to sensible and non-sensible values.)

Also from John Saunders' answer on the same question:

My "research" is due, with thanks, to SQL Server MVP Tibor Karaszi. His source is Books Online for SQL Server 6.5. Under ""Control-Of-Flow Language", RETURN", he found

"SQL Server reserves 0 to indicate a successful return and reserves negative values from - 1 through - 99 to indicate different reasons for failure. If no user-defined return value is provided, the SQL Server value is used. User-defined return status values should not conflict with those reserved by SQL Server. The values 0 through -14 are currently in use.

(Current versions of this same topic no longer have this language, but if you read the late Ken Henderson's book, The Guru's Guide to SQL Server Stored Procedures, XML, and HTML, on page 39 he indicates that -1 through -14 indicate different levels of failure (and to go read the then-current version of Books Online to see individual descriptions, a lot of good that does now), and -15 through -99 are reserved for future use.)

In the end, Erland's right; whether you get -4 or -6 or -23 doesn't really matter. If it's not 0 (and you didn't set an explicit non-zero return value to indicate success), you had an error. It sounds like you just want to ignore the PK violation error, but if you want control over what value gets returned in the event of other non-PK errors, you'll have to add your own explicit RETURN(x); command to that same IF block.

And depending on how many PK violations you expect, it might be worth considering checking for the violation manually instead of just blindly throwing everything and letting SQL Server deal with it. There can be substantial overhead with raising exceptions:

Further demonstration of where -4 (and other values) come from. Create this procedure:

CREATE PROCEDURE dbo.DemoErrors
  @ErrorLevel INT
AS
BEGIN
  RAISERROR(N'foo', @ErrorLevel, 1);
END
GO

Now if you call it with these values for @ErrorLevel (level < 11 will end up with return value of 0, and > 19 require log etc., so we'll go 11 -> 18):

SET NOCOUNT ON;
DECLARE @rc INT, @i INT = 11;
WHILE @i <= 18
BEGIN
  EXEC @rc = dbo.DemoErrors @ErrorLevel = @i;
  PRINT @rc;
  SET @i += 1;
END

Output shows that the return value is always 10 - @ErrorLevel:

Msg 50000, Level 11, State 1, Procedure DemoErrors, Line 25
foo
-1
Msg 50000, Level 12, State 1, Procedure DemoErrors, Line 25
foo
-2
Msg 50000, Level 13, State 1, Procedure DemoErrors, Line 25
foo
-3
Msg 50000, Level 14, State 1, Procedure DemoErrors, Line 25
foo
-4
Msg 50000, Level 15, State 1, Procedure DemoErrors, Line 25
foo
-5
Msg 50000, Level 16, State 1, Procedure DemoErrors, Line 25
foo
-6
Msg 50000, Level 17, State 1, Procedure DemoErrors, Line 25
foo
-7
Msg 50000, Level 18, State 1, Procedure DemoErrors, Line 25
foo
-8

(If you run as a sysadmin and add WITH LOG to the RAISERROR call, you'll see 19 produces -9, and so on, but up above that you'll start having connections severed etc. because those are serious errors.)

Related Question