Post reply

Try/Catch Raise error

  • March 8, 2016 at 10:17 am

    #1863759

    This is the catch block I have in a stored procedure, does this help?

    begin catch;

    rollback;

    select

    @ErrorNumber = ERROR_NUMBER(),

    @ErrorLine = ERROR_LINE(),

    @ErrorMessage = ERROR_MESSAGE() + '' Error occurred processing table: %s at line number: %i with original error number: %i'',

    @ErrorProcedure = ERROR_PROCEDURE(),

    @ErrorSeverity = ERROR_SEVERITY(),

    @ErrorState = ERROR_STATE();

    if @ErrorNumber = 1205 -- Trap deadlock

    begin

    WAITFOR DELAY ''00:00:00.05'' -- Wait for 50 ms

    continue;

    end

    else begin

    -- All other errors

    raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState,''@BaseTable'', @ErrorLine, @ErrorNumber) with nowait;

    end

    end catch

    NOTE: Yes, this code is in a block of dynamic SQL which is why you see two single quotes being used around string literals.

  • March 8, 2016 at 10:30 am

    #1863764

    My approach is very similar to yours in structure, other than I add all the error information into a message string

    [Code]

    @ErrorMessage = "Error message: " + @ErrMsg + " Severity: " + CAST(@Severity AS ... etc, etc

    [/Code]

    and raise my trapped error with this as the error message (usually logged to an exceptions table). Depending on your preference you can add line feeds in to make it look a little easier on the eye in a debugger when you return it to the app.

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply