sql - passing a parameter to a function; Subquery returned more than 1 value. This is not permitted

Question

I want the function to return age of the employee in specified date passed to the function by parameter

DROP FUNCTION IF EXISTS dbo.f_Employee_Age;
GO

CREATE FUNCTION f_Employee_Age(@date DATE)
RETURNS INT
AS
BEGIN
    RETURN (SELECT DATEDIFF(yy, BirthDate, @date) FROM Employee)
END;
GO

SELECT dbo.f_Employee_Age('2012-12-21')
FROM Employee
WHERE FirstName LIKE 'Andrew';

When I try to do it without the function it works perfectly fine

SELECT DATEDIFF(yy, BirthDate, '2012-12-21')
FROM Employee
WHERE FirstName LIKE 'Andrew';

But if I pass the date parameter to a function I get this error:

Msg 512, Level 16, State 1, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Answer 1

I want the function to return age of the employee in specified date passed to the function by parameter

The key here is the employee. The code that works would be clearer if you wrote:

SELECT DATEDIFF(year, e.BirthDate, '2012-12-21')
----------------------^ this is a value from a single row
FROM Employee e
WHERE e.FirstName LIKE 'Andrew';

You need to pass both values in:

CREATE FUNCTION f_Employee_Age (
    @dob DATE,
    @date DATE
)
RETURNS INT AS
BEGIN
    RETURN(DATEDIFF(year, @dob, @date);
END;

Or perhaps identify the employee:

CREATE FUNCTION f_Employee_Age (@Employee_Id @date DATE)
RETURNS INT
AS
BEGIN
    RETURN(SELECT DATEDIFF(yy, BirthDate, @date)
           FROM Employee e
           WHERE e.Employee_Id = @Employee_Id
END;

You would call this version as:

dbo.f_Employee_Age(e.Employee_Id, '2012-12-21')

Alternatively, you could create an inline table-valued function that returns the age for all employees as of that date:

CREATE FUNCTION f_Employee_Age (
    @date DATE
)
RETURNS TABLE
    RETURN (SELECT e.*, DATEDIFF(year, e.BirthDate, @date)
            FROM employees e
           );

This would be called as:

SELECT ea.age
FROM  dbo.f_Employee_Age ea('2012-12-21') ea
WHERE ea.FirstName LIKE 'Andrew';

In a table-valued inline function in many ways acts like a parameterized view.