08. Delete Employees and Departments

-- alter table Departments set ManagerID to be NULLABLE
-- delete from EmployeesProjects
-- update Department set ManagerID to be NULL
-- !remove manager from managing other dept employees (set to NULL)
-- delete from Employees
-- delete Department

CREATE PROC usp_DeleteEmployeesFromDepartment (@departmentId INT)
AS
ALTER TABLE Departments
ALTER COLUMN ManagerID INT /*makes it nullable (not specified as 'NOT NULL')*/

DELETE FROM EmployeesProjects
WHERE EmployeeID IN (SELECT EmployeeID FROM Employees WHERE DepartmentID = @departmentId) /*selects records from Employees table*/

UPDATE Departments
SET ManagerID = NULL
WHERE DepartmentID = @departmentId

UPDATE Employees
SET ManagerID = NULL
WHERE ManagerID IN (SELECT EmployeeID from Employees WHERE DepartmentID = @departmentId) /*removing the manager of the selected department from other dept employee records*/

DELETE FROM Employees
WHERE DepartmentID = @departmentId

DELETE FROM Departments
WHERE DepartmentID = @departmentId

SELECT COUNT(*)
  FROM Employees
 WHERE DepartmentID = @departmentId