SQL函数返回任何结果，但功能内容返回结果

Question

我有以下SQL函数定义和测试：

IF EXISTS (SELECT * 
     FROM sys.objects 
     WHERE object_id = OBJECT_ID(N'[dbo].[udf_Assessment_Timelines]') 
       AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')) 
    DROP FUNCTION [dbo].udf_Assessment_Timelines 
GO 
CREATE FUNCTION dbo.udf_Assessment_Timelines 
(
    @startDate datetime, 
    @endDate datetime, 
    @assessmentId int = 14, 
    @outIds nvarchar(max)='3,9,10' 
) 
RETURNS @result TABLE(case_id int, [TOTAL DAYS] int) 
AS 
BEGIN 
    DECLARE @in TimeLineReportList; 
    DECLARE @out TimeLineReportList; 

    INSERT INTO @in 
     SELECT * FROM dbo.udf_First_Timeline_Entries_Of_Status(@assessmentId) 

    INSERT INTO @out 
     SELECT * FROM dbo.udf_First_Timeline_Entries_Of_Any_Statuses(@outIds) 

    INSERT INTO @result 
     SELECT * FROM dbo.udf_Generic_Timelines(@startDate, @endDate, @in, @out,null) 

    RETURN 
END 
GO 

SELECT * FROM dbo.udf_Assessment_Timelines('2013-01-01T00:00:00.000', '2014-01-01T00:00:00.000',null,null) 

调用该函数返回0行的最后一行。但是，下面的SQL直接执行时会返回行。有什么不同？

DECLARE @result TABLE(case_id int, [TOTAL DAYS] int)  
DECLARE @in TimeLineReportList; 
DECLARE @out TimeLineReportList; 

INSERT INTO @in 
    SELECT * FROM dbo.udf_First_Timeline_Entries_Of_Status(14) 

INSERT INTO @out 
    SELECT * FROM dbo.udf_First_Timeline_Entries_Of_Any_Statuses('3,9,10') 

INSERT INTO @result 
    SELECT * FROM dbo.udf_Generic_Timelines('2013-01-01T00:00:00.000', '2014-01-01T00:00:00.000', @in, @out,null) 

SELECT * FROM @result 

Answer 1

你覆盖@assessmentId和@outIds是在不工作的版本null。

SELECT * FROM dbo.udf_Assessment_Timelines('2013-01-01T00:00:00.000', 
    '2014-01-01T00:00:00.000', null, null) 
          ^ ^

如果你想使用的默认值的参数，使用default关键字来代替：

SELECT * FROM dbo.udf_Assessment_Timelines('2013-01-01T00:00:00.000', 
    '2014-01-01T00:00:00.000', default, default) 
          ^ ^