I have been having trouble with the check in this problem.
so far I have be able to return the table but not introduce a check into the function. I can do it if the function returns an integer or creating one in a procedure. Here is my code so far:
alter function NumofEmply(@department char(25))
(select count(*) #ofEmp, pc.empnum
from employee inner join pc on employee.empnum = pc.empnum
where location = @department
group by pc.empnum
having count(*) in
inner join pc on employee.empnum = pc.empnum
group by location
having count(*) <= 1)
from dbo.NumofEmply('info sys')
Updated answer - thanks Sean for pointing out conditionals aren't allowed in a RETURN.
ALTER FUNCTION NumofEmply(@Department CHAR(25)) RETURNS @EmployeePCs TABLE ( EmpNum INT, PcCount INT, StatusMessage VARCHAR(100) ) AS BEGIN IF (EXISTS(SELECT * FROM Employee WHERE Location = @Department)) BEGIN INSERT INTO @EmployeePCs SELECT PC.EmpNum, COUNT(PC.EmpNum) AS PCCount, 'Department was found.' AS StatusMessage FROM Employee E INNER JOIN PC ON E.EmpNum = P.EmpNum WHERE Location = @Department GROUP BY pc.empnum HAVING COUNT(PC.EmpNum) < 2 END ELSE BEGIN INSERT INTO @EmployeePCs SELECT 0 AS EmpNum, 0 AS PCCount, 'No employees in that department!' AS StatusMessage END RETURN END