创建标量值UDF

--
--3.7.2节示例
--

--创建标量值UDF
USE AdventureWorks
GO
CREATE FUNCTION dbo.CalcAge(@birthday datetime) --函数名和函数的参数定义
RETURNS int --返回类型
AS
BEGIN
 RETURN year(getdate()) - year(@birthday) --用当前的年减去出生的年就是年龄
END

--查询中执行嵌入式UDF
SELECT e.LoginID,dbo.CalcAge(e.BirthDate) AS Age --使用用户定义函数
FROM HumanResources.Employee e
ORDER BY Age

--
--3.7.3节示例
--

--创建表值函数：
CREATE FUNCTION udf_GetEmployeeDepartment() --定义函数名和参数
RETURNS TABLE --返回的是一个表
AS
RETURN
(--以下是返回的内容
SELECT e.EmployeeID,e.LoginID,d.DepartmentID,d.Name AS DepartmentName
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeDepartmentHistory edh
ON e.EmployeeID=edh.EmployeeID
INNER JOIN HumanResources.Department d
ON edh.DepartmentID=d.DepartmentID
)

--查询表值函数
SELECT *
FROM udf_GetEmployeeDepartment()
-- 查询表值函数
SELECT *
FROM udf_GetEmployeeDepartment() udf
INNER JOIN HumanResources.EmployeeAddress a
ON udf.EmployeeID=a.EmployeeID
--将表值函数与其他表进行JION操作，这是存储过程无法实现的

--
--3.7.4节示例
--

--修改标量值函数
ALTER FUNCTION [dbo].[CalcAge]
(@birthday char(8)) --修改了类型
RETURNS int
AS
BEGIN
 RETURN year(getdate()) - CONVERT(int,left(@birthday,4))
END

--修改表值函数
ALTER FUNCTION [dbo].[udf_GetEmployeeDepartment]
(@groupName nvarchar(50)) --增加了参数
RETURNS TABLE
AS
RETURN
(
 SELECT e.EmployeeID,e.LoginID,d.DepartmentID,d.Name AS DepartmentName
 FROM HumanResources.Employee e
 INNER JOIN HumanResources.EmployeeDepartmentHistory edh
 ON e.EmployeeID=edh.EmployeeID
 INNER JOIN HumanResources.Department d
 ON edh.DepartmentID=d.DepartmentID
 WHERE d.GroupName=@groupName
)

--
--3.7.5节示例
--

DROP FUNCTION udf_GetEmployeeDepartment,CalcAge