title: sql multiple updates in stored procedure
body: <p>What is the best way to handle return values when using multiple update statements in a stored procedure.</p>
  
  <p>I am using this stored procedure in a data adapter and am trying to get the update result total.</p>
  
  <p>I'm using <strong>SQL Server 2008</strong> and .NET 4.5</p>
  
  <p>For example:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[usrsp_MessageConversation_delete]
  (
      @fromMbrID int,
      @toMbrID int
  )
  AS
  BEGIN
  SET NOCOUNT OFF;
  UPDATE mbr_Messages
  SET  fromDeleted = 1
  WHERE (fromMbrID = @fromMbrID) AND (toMbrID = @toMbrID)
  
  UPDATE mbr_Messages
      SET toDeleted = 1
  WHERE (fromMbrID = @toMbrID) AND (toMbrID = @fromMbrID)
  
  //RETURN TOTAL NUMBER OF UPDATED RECORDS
      EG: RETURN  @@ROWCOUNT ??
  
  END
  </code></pre>
  
  <p>I want to return the total number of updated records or 0 if non were updated.</p>
  
  <p>Do I need to add any other error handling - if so any suggestions?</p>
  
  <p><strong>UPDATE: I ended up going with this:</strong></p>
  
  <pre><code>BEGIN
  
  SET NOCOUNT ON;
  
  DECLARE @rowCounter INT;
  
  BEGIN TRY 
  
      UPDATE 
          mbr_Messages
      SET 
          fromDeleted = 1
      WHERE 
          (fromMbrID = @fromMbrID) AND (toMbrID = @toMbrID) AND (fromDeleted = 0);
  
      SET @rowCounter = @@ROWCOUNT;
  
      UPDATE 
          mbr_Messages
      SET 
          toDeleted = 1
      WHERE 
          (fromMbrID = @toMbrID) AND (toMbrID = @fromMbrID) AND (toDeleted = 0);
  
      SET @rowCounter = @rowCounter + @@ROWCOUNT;
  
  END TRY
  BEGIN CATCH
      PRINT 'Update failed';
  END CATCH 
  
  RETURN @rowCounter
  
  END
  </code></pre>