Click here to Skip to main content
11,429,154 members (66,299 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
This is my Stored procedure, which On specifying the Department ID, it deletes all the records for that particular ID from the Departments table and it also deletes all the records from the Employees table for that particular department ID.

The condition is to delete record from either both the tables or none.

My code deletes record from one table alone. i dont get where the problem occurs.

Note: There is no foreign key between Employees table and department table.
but there is a foreign key between Employees table and Accounts table.

CREATE PROCEDURE usp_DeleteDepartment (@DepartmentId INT)
AS
 
BEGIN TRAN
 
DELETE FROM Departments_Details
WHERE DepartmentId = @DepartmentId
 
IF (@@ERROR <> 0) GOTO ERROR 
 
DELETE FROM Employees_Details
WHERE DepartmentId = @DepartmentId
 
IF (@@ERROR <> 0) GOTO ERROR 
 
COMMIT TRAN
 
ERROR:
IF (@@ERROR <> 0) BEGIN
PRINT 'Foreign key constraint error occurred!'
    ROLLBACK TRAN
END
Posted 8-Oct-12 22:17pm
Edited 8-Oct-12 22:19pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

you can use try catch instead of @@ERROR.
see below code :

BEGIN   
   SET NOCOUNT ON;
   BEGIN TRY
      BEGIN TRAN
         /*
            Insert your Code Here.
         */
      COMMIT TRAN
   END TRY
   BEGIN CATCH         
      ROLLBACK TRANSACTION      
   END CATCH
END
  Permalink  
Comments
bolshie6 at 9-Oct-12 4:16am
   
Thank you, I solved it myself. I've also done it in the same way.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

ALTER PROCEDURE usp_DeleteDepartment (@DepartmentId INT)
AS
 
BEGIN TRANSACTION Trans
BEGIN TRY
 
DELETE FROM Departments_Details
WHERE DepartmentId = @DepartmentId
 
DELETE FROM Employees_Details
WHERE DepartmentId = @DepartmentId
 
COMMIT TRANSACTION Trans
END TRY
 
BEGIN CATCH
    ROLLBACK TRANSACTION Trans
END CATCH   
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web04 | 2.8.150428.2 | Last Updated 9 Oct 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100