KingSneaky KingSneaky - 7 months ago 8
SQL Question

Check an Input Parameter in a function that returns tables SQL Server 2008

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))
returns table
as
return
(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
(select count(*)
from employee
inner join pc on employee.empnum = pc.empnum
group by location
having count(*) <= 1)
)

select *
from dbo.NumofEmply('info sys')


How can I add a way to check if the input to the function is valid? 'check department'

Answer

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
Comments