title: SQL Delete with Transaction
body: <p>I have a table that has id, name, age. (Table name : Employee)</p>
  
  <p>The id is the primary key in the table. There is an Sproc which deletes the entry given the name.
  So in my sproc for deletion, I first select the id based on the name. If the name is valid then I do a delete. </p>
  
  <pre><code>DECLARE @Id uniqueidentifier
  BEGIN TRANSACTION
      SELECT TOP 1 @Id=Id FROM Employee WHERE Name=@Name
      IF @@ROWCOUNT = 0
      BEGIN
          ROLLBACK TRANSACTION
          RETURN ERROR_NOT_FOUND
      END                 
      DELETE FROM EMPLOYEE WHERE Id = @Id            
      IF @@ROWCOUNT = 0
      BEGIN
          ROLLBACK TRANSACTION
          RETURN ERROR_NOT_FOUND
      END
  COMMIT TRANSACTION
  </code></pre>
  
  <p>My question is whether I need to need a transaction in this case or not. I understand that we need a transaction when we want atomic operations (set of operations should pass/fail together).</p>
  
  <p>Please comment for the above scenario whether a transaction is required.. and what are the pros / cons of with / without transaction.</p>