title: Stored Procedure Transaction
body: <p>I have never used a Transaction, Commit and Rollback before and now I need to use one.  I have checked around online, etc for examples to make sure that I am in fact using this correctly but I am still not sure if I have coded this correct.  I am hoping someone can review and advise me if this seems correct. </p>
  
  <p>Basically I have 2 databases for an application. One is an archive - meaning data that is no longer going to be manipulated by the users will be moved to this DB.  But in the event they ever need it, I will move the needed data back to the main database for use.  My stored proc is below:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[spReopenClosed] 
  (
      @Return_Message VARCHAR(1024) = ''  OUT,        
      @IID        uniqueidentifier,
      @OpenDate   smalldatetime,
      @ReopenedBy uniqueidentifier
  )
  AS
  BEGIN
      SET NOCOUNT ON;
  
  /******************************
  *  Variable Declarations
  *******************************/
      DECLARE     @ErrorCode  int  
  
  
  
  /******************************
  *  Initialize Variables
  *******************************/
  
      SELECT @ErrorCode = @@ERROR
  
      IF @ErrorCode = 0
  
      BEGIN TRANSACTION
          /****************************************************************************
          * Step 1
          * Copy the Closed from the Archive
          ****************************************************************************/
          INSERT INTO OPS.dbo.SM_T_In
          SELECT          
          FROM OPS_ARCHIVE.Archive.SM_T_In    W
          WHERE W.GUID = @IID
              AND W.OpenDate = @OpenDate
  
  
          IF @ErrorCode &lt;&gt; 0
              BEGIN
                  -- Rollback the Transaction
                  ROLLBACK
  
                  RAISERROR ('Error in Copying from the archive', 16, 1)
                  RETURN
              END
  
  
          /****************************************************************************
          * Step 2
          * copy the notes
          ****************************************************************************/
          INSERT INTO OPS.dbo.SM_T_Notes
          SELECT 
          FROM OPS_ARCHIVE.Archive.SM_T_Notes W
          WHERE W.GUID = @IID
  
          IF @ErrorCode &lt;&gt; 0
              BEGIN
                  -- Rollback the Transaction
                  ROLLBACK
  
                  RAISERROR ('Error in copying the notes', 16, 1)
                  RETURN
              END
  
          /****************************************************************************
          * Step 3
          * Delete the from the Archive - this will also delete the notes
          ****************************************************************************/
          DELETE
          FROM OPS_ARCHIVE.Archive.SM_T_In
          WHERE OPS_ARCHIVE.Archive.SM_T_In.GUID = @IID
  
          IF @ErrorCode &lt;&gt; 0
              BEGIN
                  -- Rollback the Transaction
                  ROLLBACK
  
                  RAISERROR ('Error in deleting the items from the Archive', 16, 1)
                  RETURN
              END
  
          COMMIT
  
          BEGIN
              SELECT  @ErrorCode  = @@ERROR
  
              IF @ErrorCode = 0
                      SELECT  @Return_Message = 'All data was moved over'
          END
  
  
  
  /*************************************
  *  Get the Error Message for @@Error
  *************************************/
      IF @ErrorCode &lt;&gt; 0
      BEGIN
          SELECT  @Return_Message = [Description]     -- Return the SQL Server error
            FROM  master.dbo.SYSMESSAGES
           WHERE  error = @ErrorCode
      END
  
  /*************************************
  *  Return from the Stored Procedure
  *************************************/
      RETURN @ErrorCode                               -- =0 if success,  &lt;&gt;0 if failure
  END
  </code></pre>
  
  <p>I have two inserts that move the data from 2 tables from the Archive database.  If those inserts are successful, then I will delete the data from the Archive DB.  I would appreciate any feedback on this, I need to make sure that I am doing this properly.</p>
  
  <p>Thanks</p>