08. Delete Employees and Departments

CREATE PROC usp_DeleteEmployeesFromDepartment (@departmentId INT) AS
ALTER TABLE Departments
ALTER COLUMN ManagerID INT

ALTER TABLE Employees
ALTER COLUMN DepartmentID INT

ALTER TABLE Employees
ALTER COLUMN AddressID INT

DELETE FROM EmployeesProjects
WHERE EmployeeID IN (SELECT EmployeeID FROM Employees WHERE DepartmentID=@departmentId)

UPDATE Employees
SET DepartmentID = NULL
WHERE DepartmentID = @departmentId

UPDATE Employees
SET AddressID = NULL
WHERE DepartmentID = @departmentId

DELETE FROM Employees
WHERE DepartmentID = @departmentId AND ManagerID IS NULL

UPDATE Departments
SET ManagerID = NULL
WHERE DepartmentID = @departmentId

DELETE FROM Departments
WHERE DepartmentID=@departmentId

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