Sunday 25 March 2012

Roll back and transaction in sql server procedure


CREATE PROCEDURE DeleteDepartment
(
   @DepartmentID    int
)
AS

-- This sproc performs two DELETEs.  First it deletes all of the
-- department's associated employees.  Next, it deletes the department.

-- STEP 1: Start the transaction
BEGIN TRANSACTION

-- STEP 2 & 3: Issue the DELETE statements, checking @@ERROR after each statement
DELETE FROM Employees
WHERE DepartmentID = @DepartmentID

-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Error in deleting employees in DeleteDepartment.', 16, 1)
    RETURN
 END


DELETE FROM Departments
WHERE DepartmentID = @DepartmentID

-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Error in deleting department in DeleteDepartment.', 16, 1)
    RETURN
 END

-- STEP 4: If we reach this point, the commands completed successfully
--         Commit the transaction....
COMMIT

No comments:

Post a Comment