Need to include columns in a user defined function and store it in a procedure

Question

In Microsoft SQL Server Management Studio I created a user defined function to calculate an employee's age based on the date of birth the user inputs as follows:

USE [Northwind];
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION udf_GetEmployeeAge_test2
(
@DateOfBirth datetime
)
RETURNS int 
AS
BEGIN
DECLARE @Age int
SELECT @Age = DATEDIFF(DAY, @DateOfBirth, GETDATE())
RETURN @Age
END

I'm using the popular sample Northwind database, now the thing I can't seem to figure out is how and where do I include a select statement to return each employee name(FirstName),

surname(LastName),

date of birth(BirthDate)

and age and then also wrap the Select statement in a stored procedure(usp_EmployeeAges).

The info for these columns are in a table called dbo.Employees

`select name,surname,BirthDate,Age=dbo.udf_GetEmployeeAge_test2(BirthDate) from yourtable` but Age calculation is not correct — Pரதீப், Jan 22 '17 at 14:13
Use this `FLOOR((CAST (GetDate() AS INTEGER) - CAST(BirthDateAS INTEGER)) / 365.25)` to calculate age. Check this question [How to calculate age (in years) based on Date of Birth and getDate()](http://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate) — Pரதீப், Jan 22 '17 at 14:15

score 0 · Accepted Answer · edited Nov 13 '18 at 22:53

Increase the performance by switching to using an inline table-valued function instead of a scalar valued function.

Instead of creating a scalar UDF with a return clause like:

return @Age

Create an inline table-valued UDF with a return clause like:

return select Age = <expression>

In the query, instead of:

age = dbo.udf_GetEmployeeAge(col1)

Use:

age = (select age from dbo.udf_GetEmployeeAge(col1))

Example inline table-valued UDF for Age:

create function dbo.udf_GetEmployeeAge (@DateOfBirth datetime) 
  returns table as return
  select Age = (convert(int,convert(char(8),getdate(),112))
                -convert(char(8),@DateOfBirth,112)
                ) / 10000;
go

select *
    , Age=(select Age from dbo.udf_GetEmployeeAge(Birth_Date))
  from pilots

Test setup on rextester: http://rextester.com/CDAEI21728

To use it in a stored procedure, you use it the same way you would in a query.

create procedure dbo.get_Pilots_WithAge (@id int) as 
begin
select *
    , Age=(select Age from dbo.udf_GetEmployeeAge(Birth_Date))
  from pilots
  where id = @id;
end;
go

exec dbo.get_Pilots_WithAge @id=1;

Reference:

Need to include columns in a user defined function and store it in a procedure

1 Answers1