CODEWITHSUNDEEP
sql
Venki
Venki

Reputation: 2169

SQL Delete with Transaction

I have a table that has id, name, age. (Table name : Employee)

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. 

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

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).

Please comment for the above scenario whether a transaction is required.. and what are the pros / cons of with / without transaction.

Upvotes: 0

Views: 26331

Answers (2)

anton.malinovskiy
anton.malinovskiy

Reputation: 26

As for the answer to your question - if there is no employee with this name neither delete nor select will not change anything in the database, thus rolling back is not necessary.

Now if id is unique and the name is not - selecting an employee by the name is pretty dangerous since you have no real control which employee (from ones with the same name) you are going to delete. Looks like this procedure should take id as a parameter, rather than selecting it by name.

Upvotes: 1

Jeff Swensen
Jeff Swensen

Reputation: 3573

In your example code both ROLLBACK statements aren't actually rolling anything back since in both cases you haven't changed anything in the database. In other words, no, they aren't necessary.

Upvotes: 1

Related Questions