T-SQL函数能否在部分场景返回行集、部分场景返回标量值？

最近有学生提了个作业需求：要创建函数（不是存储过程），根据传入参数用WHERE子句返回行集；同时要求“如果结果为null就返回0”。我对这个需求有几个疑问：

• TVF（内联或多值表值函数）不会返回NULL，最多是返回空结果集，这是常识；
• 存储过程可以通过返回值实现类似逻辑，但需求明确要求用函数；
• 就算TVF能返回0，把它当查询数据源用的时候（不管是单独查还是关联其他表）会有逻辑矛盾，比如用标量值去关联行集。

有没有我没注意到的T-SQL新特性能实现这个需求？

存储过程实现示例 #

CREATE PROCEDURE EmployeeDetails
(@empID int)
AS

declare @rows int;

SELECT @rows= count(*) from Employees
 where EmployeeID=@empID;

if @rows=0
 RETURN 0;
else
 SELECT * from Employees where EmployeeID=@empID;
 RETURN @@rowcount;
go

尝试的TVF实现（存在逻辑问题） #

我自己试的TVF实现还有问题，尤其是当结果要关联其他表的主键/外键时，问题更明显：

CREATE FUNCTION dbo.EmployeeWithNullTest 
(
    @empID int
)
RETURNS 
@outputTable TABLE 
(
     employeeid int
    ,lastname varchar(20)
    ,firstname varchar(20)
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set
    insert into @outputTable
    select employeeid, firstname, lastname 
    from employees
    where employeeid=@empID

    if (select count(*) from @outputTable)=0
    insert into @outputTable  values (0,0,0)

    RETURN 
END
GO

问题解答与可行方案 #

核心结论：需求本身存在逻辑矛盾，无直接匹配的T-SQL新特性

首先明确：表值函数（TVF）永远不会返回NULL，只会返回行集（空或非空），这是T-SQL的基础设计规则，没有新特性打破这个逻辑。需求里的“结果为null则返回0”表述不准确，实际是指“无匹配行时返回0”，但这个要求和TVF的定位天然冲突。

1. 现有TVF实现的问题

你写的多语句表值函数（MTVF）在无匹配时插入(0,0,0)，会引发两个关键问题：

• 关联其他表时，0通常不是合法的外键值，会生成错误的关联结果；
• 调用者无法区分“存在EmployeeID=0的真实员工”和“无匹配时的占位行”，导致逻辑混淆。

2. 折中实现方案

如果必须用函数，只能根据需求的实际意图调整实现方式：

方案一：改用标量函数（仅返回数值，不返回行集）

如果需求的核心是“无匹配时返回0”，而非必须返回行集，可以用标量函数，但这会牺牲“返回行集”的要求：

CREATE FUNCTION dbo.GetEmployeeMatchStatus(@empID int)
RETURNS int
AS
BEGIN
    DECLARE @matchCount int
    SELECT @matchCount = COUNT(*) FROM Employees WHERE EmployeeID = @empID
    RETURN CASE WHEN @matchCount = 0 THEN 0 ELSE @matchCount END
END
GO

方案二：带状态标识的内联表值函数（兼顾行集与无匹配提示）

如果必须返回行集，同时需要标识无匹配情况，可以在结果集中增加状态列，避免用0占位破坏关联逻辑：

CREATE FUNCTION dbo.EmployeeDetailsWithStatus(@empID int)
RETURNS TABLE
AS
RETURN (
    -- 返回匹配的员工行，标记为有匹配
    SELECT 
        EmployeeID, 
        FirstName, 
        LastName,
        1 AS IsMatchFound
    FROM Employees 
    WHERE EmployeeID = @empID
    UNION ALL
    -- 无匹配时返回占位行，明确标记为无匹配
    SELECT 
        0 AS EmployeeID, 
        '' AS FirstName, 
        '' AS LastName,
        0 AS IsMatchFound
    WHERE NOT EXISTS (SELECT 1 FROM Employees WHERE EmployeeID = @empID)
)
GO

这个内联表值函数（ITVF）性能优于多语句表值函数，调用者可以通过IsMatchFound字段区分真实数据和占位行，关联其他表时只需过滤掉IsMatchFound=0的行即可避免错误。

3. 需求的本质澄清

学生的需求大概率是混淆了存储过程和函数的特性：存储过程可以用RETURN返回整数状态码，但表值函数的返回值只能是行集，无法像存储过程那样返回单独的0状态。需要先明确需求的核心目标——是要返回员工数据，还是要返回匹配状态，再选择对应的实现方式。

内容的提问来源于stack exchange，提问作者J. Barrett