Blog

Blog of me, Chris Idzerda, containing my thoughts, comments, and questions.

Using RAISERROR in a TRY-CATCH block

As Adrian pointed out in his recent post, it is not a straightforward task to get all of the error information back to the middle tier from a stored procedure using RAISERROR. The RAISERROR statement provides three user-settable parameters: message, severity, and state. Since SQL Server associates particular semantics with the severity, use one or both of the other two to provide the middle tier with specific information. Here are a couple of possibilities.

Provide a custom message by constructing an easily parsed string in the stored procedure.

    SET @ErrorMessage = ERROR_MESSAGE()
        + N':' + CAST(ERROR_NUMBER() AS nvarchar)
        + N',' + CAST(ERROR_LINE() AS nvarchar)
        + N',' + ERROR_PROCEDURE()

Parse it in the middle tier.

    catch(SqlException sqlex)
    {
        string message= sqlex.Message;
        message= message.Substring(message.LastIndexOf(':') + 1);
        string[] parts= message.Split(',');
        switch(int.Parse(parts[0]))
        {
        case 544: // Cannot insert explicit value for identity column....
          break;
        case 8134: // Divide by zero error encountered.
          break;
        case 1105: // Could not allocate space....
            switch(sqlex.State)
            {
            case 2: // data segment full
              break;
            case 3: // log segment full
              break;
            default:
              break;
            }
            break;
        default:
          break;
        }
    }
Provide a custom state by using a CASE expression in the stored procedure.

SET @ErrorState = CASE ERROR_NUMBER() WHEN 544 THEN 1 WHEN 8134 THEN 2 ELSE 99 END

If you need to differentiate states for a single error (as option #1 does), add that to the CASE expression.

    SET @ErrorState = CASE ERROR_NUMBER()
        WHEN 544 THEN 1 -- Cannot insert explicit value for identity column....
        WHEN 8134 THEN 2 -- Divide by zero error encountered.
        WHEN 1105 THEN CASE ERROR_STATE() -- Could not allocate space....
            WHEN 2 THEN 3 -- data segment full
            WHEN 3 THEN 4 -- log segment full
            ELSE 5 END
        ELSE 99 END

The middle tier can use the state value to decide what to do.

    catch(SqlException sqlex)
    {
        switch(sqlex.State)
        {
        case 1: // Cannot insert explicit value for identity column....
          break;
        case 2: // Divide by zero error encountered.
          break;
        case 3: // Could not allocate space (data segment full)....
          break;
        case 4: // Could not allocate space (log segment full)....
          break;
        default:
          break;
        }
    }