What I Have so far: CREATE FUNCTION fnInsPlans (@PlanName varchar(50)) RETURNS int BEGIN RETURN (SELECT COUNT(*) FROM EmpData WHERE TermDate IS NOT NULL (SELECT BenPlanID FROM Benefits WHERE PlanName = @PlanName)) END; 

Instructions: The return portion of the select should include one column- a count of the records in the EmpData table and should filter the results so only active employees (not terminated) are counted. The where should also utilize a subquery in the where to find the BenPlanID number based on the input parameter (@PlanName).

I am struggling with getting my subquery to work, currently it just says "Select statements included within a function cannot return data to a client". What would be the correct way to put this in order for it to work properly?

This is for SQL Server NOT MySQL