RaiseError did not raise the error enough !?

Question

Why the error does not appear at the UI layer? I am using ExecuteScaler

BEGIN CATCH

  PRINT N'The transaction is in an uncommittable state. Rolling back transaction.'
  ROLLBACK TRANSACTION;

  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;

  SELECT @ErrorMessage = ERROR_MESSAGE(),
         @ErrorSeverity = ERROR_SEVERITY(),
         @ErrorState = ERROR_STATE();

  RAISERROR (@ErrorMessage, 
             @ErrorSeverity,
             @ErrorState);

END CATCH

Thanks

Answer 1

You can only raise user messages, system messages can only be raised by the engine:

Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.

Therefore you cannot raise the original @ErrorMessage, you have to raise a new error code.

Also, your catch block is incorrect in as it assumes an uncommittable transaction. This is not always the case, you must check the result of XACT_STATE() before deciding if the transaction is doomed. There are many cases on which the error handling can continue the transaction. See Exception handling and nested transactions.

Answer 2

First, review Remus's article on error handling in nested transactions, so you understand the full scope of things.

Then, try forcing the error level to 16. Also, embed the original error information in the error message when you rethrow the error, so you don't lose that information:

BEGIN TRY
  SELECT 1/0
END TRY

BEGIN CATCH

  DECLARE 
    @ErrorMessage  NVARCHAR(4000)
  , @ErrorNumber   INT
  , @ErrorSeverity INT
  , @ErrorState    INT
  , @ErrorLine     INT

  SELECT 
    @ErrorNumber   = ERROR_NUMBER()
  , @ErrorSeverity = ERROR_SEVERITY()
  , @ErrorState    = ERROR_STATE()
  , @ErrorLine     = ERROR_LINE();

  SET @ErrorMessage 
    = '>> Msg %i, Level %i, State %i, Line %i'
    + CHAR(13)+CHAR(10)+ERROR_MESSAGE()

  RAISERROR (
    @ErrorMessage,16,1
  , @ErrorNumber
  , @ErrorSeverity
  , @ErrorState
  , @ErrorLine
  )

END CATCH