title: t-sql Return Error Codes vs RaiseError
body: <p>Hi I am writing a stored procedure that will be executing a batch of jobs on an hourly schedule and I am trying to decide whether to return errors or raise them. Assuming I would be logging the errors inside each job which would lead to better performance and maintainability?</p>
  
  <p>e.g.</p>
  
  <p>--With Error Codes</p>
  
  <pre><code>CREATE PROCEDURE Job1
  AS
  BEGIN
  
      BEGIN TRY
          --Do some work
      END TRY
      BEGIN CATCH
          --log error
          RETURN 1; --Return 1 for error
      END CATCH
      RETURN 0;
  
  END
  
  CREATE PROCEDURE USP_BatchJob
  AS
  BEGIN
      BEGIN TRANSACTION;
  
      DECLARE @Error INT;
      EXEC @Error = Job1;
      IF @Error &lt;&gt; 0
          GOTO ErrorHandling;
  
      EXEC @Error = Job1;
      IF @Error &lt;&gt; 0
          GOTO ErrorHandling;
  
      IF @@TRANCOUNT &gt; 0
          COMMIT TRANSACTION;
  
      RETURN;
  
  ErrorHandling:
      IF @@TRANCOUNT &gt; 0
          ROLLBACK;
  END
  </code></pre>
  
  <p>e.g. RaiseError</p>
  
  <pre><code>CREATE PROCEDURE Job1
  AS
  BEGIN
  
      BEGIN TRY
          --Do some work
      END TRY
      BEGIN CATCH
          --log error
          RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE());
      END CATCH
  
  END
  
  CREATE PROCEDURE USP_BatchJob
  AS
  BEGIN
      BEGIN TRANSACTION
      BEGIN TRY
  
      EXEC Job1;
  
      EXEC Job1;
  
      END TRY
      BEGIN CATCH
          IF @@TRANCOUNT &gt; 0
              ROLLBACK;
      END CATCH
  
      IF @@TRANCOUNT &gt; 0
          COMMIT TRANSACTION;
  
  END
  </code></pre>
  
  <p>The latter seems to produce more maintainable code</p>