Return error message from stored procedure

Question

The question should be quite simple, but I can't figure out the answer nor why my stored procedure is not working.

CREATE PROCEDURE spTest_Delete
@ID int
AS
    begin tran
        declare @err int
        declare @errMesage nvarchar(max)
        set @errMesage = ''
        set @err = 0

        delete from Test
        where ID = @ID

        set @err = @@ERROR
        set @errMesage = ERROR_MESSAGE()

        if @err = 0 
           commit tran
        else 
        begin
            RAISERROR(N'Could not delete !Error nr: %d. Message: %s', 1, 16, @err, @errMesage)
            rollback tran
        end

This procedure runs ok, but in case of FK constraint on the delete statement it runs into an error (which is good) and I would like to catch the error.

Msg 547, Level 16, State 0, Procedure spTest_Delete, Line 12
The DELETE statement conflicted with the REFERENCE constraint "FK_TEstFK_Test". The conflict occurred in database "Test", table "dbo.Test", column 'ID'. The statement has been terminated.

Could not delete!
Error nr: 547. Message: (null) Msg 50000, Level 1, State 16

I always get null for my message variable, even though the delete statement throws an error.

Answer 1

Try to use TRY CATCH and catch your error like this:

BEGIN TRY
   delete from Test
    where  ID = @ID
END TRY
BEGIN CATCH
    SET @ErrorMessage  = ERROR_MESSAGE()
    SET @ErrorSeverity = ERROR_SEVERITY()
    SET @ErrorState    = ERROR_STATE()
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
    BREAK
END CATCH