CODEWITHSUNDEEP
t-sqlsql-server-2008stored-procedures
chobo
chobo

Reputation: 32311

Why is ERROR_MESSAGE() always null?

I am writing a stored procedure in sql server 2008. The problem is the @ErrorMessage out parameter is always null. It seems to be related to the ERROR_MESSAGE() function, because when I get rid of that the other part of the message is returned.

How can I get it to return the entire errorMessage?

-- Log transaction
        INSERT INTO Transactions (TxnId, TypeId, [Date], Amount)
                                    VALUES(@TxnId, @TypeId, @TransDate, @Amount)

        -- Check for errors
        IF @@ERROR <> 0
        BEGIN
            PRINT 'Starting third error block'

                SET @ErrorCode = 202
            SELECT @ErrorMessage = 'Err_TxnId_Exists - Error inserting: ' + ERROR_MESSAGE()

            PRINT @ErrorCode
            PRINT @ErrorMessage
            PRINT 'Ending third error block'

            RETURN 1
        END

Messages output

The statement has been terminated. Starting third error block 202

Ending third error block

(1 row(s) affected)

Results

(1 row(s) affected)

Upvotes: 6

Views: 6122

Answers (3)

Fosco
Fosco

Reputation: 38526

ERROR_MESSAGE() is only valid inside a CATCH block.

Give this a shot:

BEGIN TRY
    INSERT INTO Transactions (TxnId, TypeId, [Date], Amount)                                     
    VALUES (@TxnId, @TypeId, @TransDate, @Amount)
END TRY
BEGIN CATCH
        SET @ErrorCode = 202              
        SET @ErrorMessage = ERROR_MESSAGE()
        PRINT @ErrorCode              
        PRINT @ErrorMessage    
END CATCH;

Upvotes: 14

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

Because "The error functions will return NULL if called outside the scope of a CATCH block."

Upvotes: 0

Justin
Justin

Reputation: 1438

I believe you use the ERROR_MESSAGE() function with TRY...CATCH blocks. Check out the usage.

Upvotes: 2

Related Questions