udfdemo13.sql

T-SQL示例大全

/* 文件名称: UDFDemo13.sql */
USE 北风贸易
GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.udf_EmployeeByDepartmentAge') and xtype in (N'FN', N'IF', N'TF'))
drop function dbo.udf_EmployeeByDepartmentAge
GO

-- 建立一个内嵌数据表值用户自定义函数
CREATE FUNCTION dbo.udf_EmployeeByDepartmentAge
(
@department nvarchar(10),
@today datetime,
@lowerage int,
@higherage int
)
RETURNS TABLE
AS
RETURN
(
SELECT 姓名,电话号码,家庭地址,部门,
              年龄 = DATEDIFF(yy,出生日期,@today)
 FROM 飞狐工作室
      WHERE 部门 = @department AND
                    DATEDIFF(yy,出生日期,@today) BETWEEN @lowerage AND @higherage
)

GO