sqlserver 标量函数例子

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

1.定义

use AdventureWorks2014;
go
if exists(select * from sys.objects where name='udf_GET_AssignedEquipment')
drop function dbo.udf_GET_AssignedEquipment;
go
create function dbo.udf_GET_AssignedEquipment
(
    @Title nvarchar(50),
    @HireDate datetime,
    @SalariedFlag bit
)
RETURNS nvarchar(50)
as
begin
    DECLARE @EquipmentType nvarchar(50)
    IF @Title LIKE 'Chief%' OR
        @Title LIKE 'Vice%' OR
        @Title = 'Database Administrator'
        begin
            SET @EquipmentType = 'PC Build A' ;
        end
    IF @EquipmentType IS NULL AND @SalariedFlag = 1
        begin
            SET @EquipmentType = 'PC Build B' ;
        end
    IF @EquipmentType IS NULL AND @HireDate < '1/1/2002'
        begin
            SET @EquipmentType = 'PC Build C' ;
        end
    IF @EquipmentType IS NULL
        begin
            SET @EquipmentType = 'PC Build D' ;
        end
    RETURN @EquipmentType ;
end
go

2.使用

Use AdventureWorks2014; 
GO
SELECT PC_Build = dbo.udf_GET_AssignedEquipment(JobTitle, HireDate, SalariedFlag),
Employee_Count = COUNT(*)
FROM HumanResources.Employee
GROUP BY dbo.udf_GET_AssignedEquipment(JobTitle, HireDate, SalariedFlag)
ORDER BY dbo.udf_GET_AssignedEquipment(JobTitle, HireDate, SalariedFlag);