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