标量函数用于传入多个参数返回一个结果
1.定义
1 use AdventureWorks2014; 2 go 3 if exists(select * from sys.objects where name=‘udf_GET_AssignedEquipment‘) 4 drop function dbo.udf_GET_AssignedEquipment; 5 go 6 create function dbo.udf_GET_AssignedEquipment 7 ( 8 @Title nvarchar(50), 9 @HireDate datetime, 10 @SalariedFlag bit 11 ) 12 RETURNS nvarchar(50) 13 as 14 begin 15 DECLARE @EquipmentType nvarchar(50) 16 IF @Title LIKE ‘Chief%‘ OR 17 @Title LIKE ‘Vice%‘ OR 18 @Title = ‘Database Administrator‘ 19 begin 20 SET @EquipmentType = ‘PC Build A‘ ; 21 end 22 IF @EquipmentType IS NULL AND @SalariedFlag = 1 23 begin 24 SET @EquipmentType = ‘PC Build B‘ ; 25 end 26 IF @EquipmentType IS NULL AND @HireDate < ‘1/1/2002‘ 27 begin 28 SET @EquipmentType = ‘PC Build C‘ ; 29 end 30 IF @EquipmentType IS NULL 31 begin 32 SET @EquipmentType = ‘PC Build D‘ ; 33 end 34 RETURN @EquipmentType ; 35 end 36 go
2.使用
1 Use AdventureWorks2014; 2 GO 3 SELECT PC_Build = dbo.udf_GET_AssignedEquipment(JobTitle, HireDate, SalariedFlag), 4 Employee_Count = COUNT(*) 5 FROM HumanResources.Employee 6 GROUP BY dbo.udf_GET_AssignedEquipment(JobTitle, HireDate, SalariedFlag) 7 ORDER BY dbo.udf_GET_AssignedEquipment(JobTitle, HireDate, SalariedFlag);