CREATE PROCEDURE AL_EVALUATE_ERROR
@ErrorNumber int OUTPUT,
@ErrorSeverity int OUTPUT,
@ErrorState int OUTPUT,
@ErrorMessage nvarchar(max) OUTPUT,
@ApplicationErrorCode int OUTPUT
AS
BEGIN
    
    /************************************************/
    /*                                              */
    /* This procedure maps SQL Server exceptions to */
    /* Application Error Codes.  There is no Oracle */
    /* equivalent to this procedure.                */
    /*                                              */
    /************************************************/

    -- When translation not found ApplicationErrorCode
    -- should remain NULL as a flag for such
    SET @ApplicationErrorCode = NULL

    -- Query Error Details
    SELECT
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorMessage = ERROR_MESSAGE();

    -- Evaluate Custom Errors
    -- Custom Errors will have ErrorNumber 50000
    IF (@ErrorNumber = 50000)            
    BEGIN
        IF (@ErrorState = 0) -- value_doesnot_exist
            SET @ApplicationErrorCode = -20000
        IF (@ErrorState = 1) -- already_updated
            SET @ApplicationErrorCode = -20001
        IF (@ErrorState = 2) -- application_not_null_violation
            SET @ApplicationErrorCode = -20002
        IF (@ErrorState = 3) -- application_unique_violation
            SET @ApplicationErrorCode = -20003
        IF (@ErrorState = 4) -- application_foreign_key_violation
            SET @ApplicationErrorCode = -20004
        IF (@ErrorState = 5) -- child_dependency_violation
            SET @ApplicationErrorCode = -20005
        IF (@ErrorState = 6) -- global_exists_exception
            SET @ApplicationErrorCode = -20006
        IF (@ErrorState = 11) -- field_already_exists
            SET @ApplicationErrorCode = -20011
        IF (@ErrorState = 15) -- subtype_value_notpresent
            SET @ApplicationErrorCode = -20015
        IF (@ErrorState = 16) -- primarykey_deleted
            SET @ApplicationErrorCode = -20016
        IF (@ErrorState = 45) -- roleattached_exception
            SET @ApplicationErrorCode = -20045
        IF (@ErrorState = 100) -- auth_search_doesnot_exist
            SET @ApplicationErrorCode = -20100
    END
    -- Evaluate System Errors
    ELSE IF (@ErrorNumber = 2627 OR @ErrorNumber = 2601) -- system_unique_violation
        SET @ApplicationErrorCode = -1
    ELSE IF (@ErrorNumber = 547) -- foreign_key_violation
        SET @ApplicationErrorCode = -2291 -- also -2292
--        ELSE IF (@ErrorNumber = 547) -- child_rec_exception
--            SET @ApplicationErrorCode = -2292
    ELSE IF (@ErrorNumber = 515) -- system_not_null_violation
        SET @ApplicationErrorCode = -1400

    RETURN
END
GO