sqlserver 标量函数例子

时间:2022-03-27 01:43:57

标量函数用于传入多个参数返回一个结果

sqlserver 标量函数例子

 

 

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);