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

You might want to start using TRY..CATCH block in your procedures

Implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

So your procedure could be rewritten as:

CREATE PROCEDURE spTest_Delete @ID INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
            DELETE
            FROM Test
            WHERE ID = @ID;
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION
        SELECT ERROR_NUMBER(), ERROR_MESSAGE();
    END CATCH
END

Also, please note that you're running as single delete statement. It means that it doesn't need to be wrapped up in a transaction. This question explains why.

Your code becomes this:

CREATE PROCEDURE spTest_Delete @ID INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DELETE
        FROM Test
        WHERE ID = @ID;
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER(), ERROR_MESSAGE();
    END CATCH
END

Now why your @errMessage is always NULL? Because ERROR_MESSAGE() is valid ONLY IN CATCH BLOCK. That's written in documentation:

Returns the message text of the error that caused the CATCH block of a TRY…CATCH construct to be run.

Using TRY..CATCH in Transact-SQL tells this:

Error information is retrieved by using these functions from anywhere in the scope of the CATCH block of a TRY…CATCH construct. The error functions will return NULL if called outside the scope of a CATCH block.

Answer 2

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