12. Assign Employee Task (Service Database,)

-- 12.	Assign Employee

CREATE PROCEDURE usp_AssignEmployeeToReport(@EmployeeId INT, @ReportId INT)
              AS
            BEGIN
                  DECLARE @employeeDepartmentId INT = (SELECT DepartmentId FROM Employees WHERE Id LIKE @EmployeeId)

                  DECLARE @categoryDepartmentId INT = (SELECT d.Id AS CategoryDepartmentId FROM Reports AS r
                                    JOIN Categories AS c
                                    ON r.CategoryId = c.Id
                                    JOIN Departments AS d
                                    ON c.DepartmentId = d.Id
									WHERE r.Id LIKE @ReportId)

                            IF(@employeeDepartmentId<>@categoryDepartmentId)
                            THROW 500001, 'Employee doesn''t belong to the appropriate department!', 1

                             UPDATE Reports
                                 SET EmployeeId = @EmployeeId
                               WHERE Id LIKE @ReportId
                END

-- Example

EXEC usp_AssignEmployeeToReport 30, 1

EXEC usp_AssignEmployeeToReport 17, 2