Why is ERROR_MESSAGE () always null?

Question

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

How can I make it 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 

Message output

Application completed. Starting the third error block 202

Completion of the third block of errors

(1 row (s) affected)

results

• @ErrorCode = 202
• @ErrorMessage = null

(1 row (s) affected)

Answer 1

ERROR_MESSAGE () is only valid inside the CATCH block.

Take a picture:

 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; 

Answer 2

I believe that you are using the ERROR_MESSAGE() function with TRY...CATCH blocks. Check usage .

Answer 3

Because " Error functions return NULL if called outside the scope of CATCH. "