Table of SQL Errors & Stored Procedures..

Question

I have this bit of code I found on the web at the end of each of my stored procedures:

ROLLBACK TRANSACTION

      PRINT '-----START: ERROR DETAILS-----'
      PRINT ERROR_NUMBER()
      PRINT ERROR_SEVERITY()
      PRINT ERROR_STATE()
      PRINT ERROR_PROCEDURE()
      PRINT ERROR_LINE()
      PRINT ERROR_MESSAGE()
      PRINT '-----END: ERROR DETAILS-----'

      DECLARE @prmErrmsg NVARCHAR(4000);
      DECLARE @prmErrSvr INT;

      SELECT @prmErrmsg = ERROR_MESSAGE()
       ,@prmErrSvr = ERROR_SEVERITY();

      INSERT INTO dbo.myErrors
         ([ErrorMessage]
         ,[ErrorSeverity]
         ,[DateCreated])
        VALUES
         (@prmErrmsg
         ,@prmErrSvr
         ,GetutcDate())


      RAISERROR(@prmErrmsg,@prmErrSvr,1)

It's writing entries to the myErrors table, but it's not writing an error message or an error severity.

My first question is why?

I believe it's got something to with the error severity being within a certain range.

How can I tell it to log verbose error messages ALWAYS, regardless of severity?

Answer 1

According to MSDN, ERROR_MESSAGE() etc are only set within the CATCH block of a TRY ... CATCH statement.