title: SQL How to; Try-Catch error info to error-log table, for stored proc executed within a transaction?
body: <p>In my stored procs, I am using Try-Catch, and calling an error handling procedure in the Catch block that will log the error details in a ErrorLog table, then rethrow the error.</p>
  
  <p>In my C# code, I am executing my stored procedures using:</p>
  
  <pre class="lang-csharp prettyprint-override"><code>using(TransactionScope scope = new TransactionScope()) {
      // execute stored procs
      scope.Complete();
  }
  </code></pre>
  
  <p>The problem I'm having, if the transaction is aborted (scope.Complete is never called), my error handling stored proc does rethrow the sql error, but can't log the error into the ErrorLog table because it is in the context of Transaction; Is there ANY way around this !? I already know that data cannot be inserted when a transaction is in an uncommittable state, so how do I exit the transaction and still log the error ?</p>
  
  <p>TSQL code:</p>
  
  <pre><code>BEGIN PROCEDURE [dbo].[usp_UpsertSomething]
      @SomethingID BIGINT
  AS
  BEGIN
      SET NOCOUNT ON;
  
      BEGIN TRY
          -- do something
      END TRY
      BEGIN CATCH
          EXEC dbo.cp_RethrowError
          RETURN -1
      END CATCH;
  END
  
  CREATE PROCEDURE [dbo].[usp_RethrowError]
      @ErrorLogID [INT] = 0 OUTPUT -- Contains the ErrorLogID of the row inserted
                                   -- by cp_RethrowError in the ErrorLog table.
  AS
  BEGIN
      -- Return if there is no error information to retrieve.
      IF ERROR_NUMBER() IS NULL
          RETURN;
  
      DECLARE 
          @ErrorMessage           VARCHAR(4000),
          @FormattedErrorMessage  VARCHAR(4000),
          @ErrorNumber            INT,
          @ErrorSeverity          INT,
          @ErrorState             INT,
          @ErrorLine              INT,
          @ErrorProcedure         VARCHAR(200);
  
      -- Assign variables to error-handling functions that 
      -- capture information for RAISERROR.
      SELECT 
          @ErrorNumber = ERROR_NUMBER(),
          @ErrorMessage = ERROR_MESSAGE(),
          @ErrorSeverity = ERROR_SEVERITY(),
          @ErrorState = ERROR_STATE(),
          @ErrorLine = ERROR_LINE(),
          @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
  
      -- Build the message string that will contain original
      -- error information.
      SELECT @FormattedErrorMessage = 
          'ErrorLogID %d, Error %d, Level %d, State %d, Procedure %s, ' + 
              'Line %d, Message: '+ @ErrorMessage;
  
      BEGIN TRY
          -- Data insertion/modification is not allowed when 
          -- a transaction is in an uncommittable state.
          IF XACT_STATE() = -1 BEGIN
              SET @ErrorLogID = 0;
          END
          ELSE BEGIN
              INSERT [dbo].[ErrorLog] 
              (
                  ErrorNumber, 
                  ErrorSeverity, 
                  ErrorState, 
                  ErrorProcedure, 
                  ErrorLine, 
                  ErrorMessage
              ) 
              VALUES 
              (
                  @ErrorNumber,
                  @ErrorSeverity,
                  @ErrorState,
                  @ErrorProcedure,
                  @ErrorLine,
                  @ErrorMessage
              );
  
              -- Pass back the ErrorLogID of the row inserted
              SELECT @ErrorLogID = @@IDENTITY;
          END
      END TRY
      BEGIN CATCH
          PRINT 'An error occurred in stored procedure cp_RethrowError.';
      END CATCH
  
      -- Raise an error: msg_str parameter of RAISERROR will contain
      -- the original error information.
      RAISERROR 
      (
          @FormattedErrorMessage, 
          @ErrorSeverity, 
          1,
          @ErrorLogID,     -- parameter: ErrorLogID in ErrorLog table.
          @ErrorNumber,    -- parameter: original error number.
          @ErrorSeverity,  -- parameter: original error severity.
          @ErrorState,     -- parameter: original error state.
          @ErrorProcedure, -- parameter: original error procedure name.
          @ErrorLine       -- parameter: original error line number.
      );
  END
  </code></pre>