Sql 函数问题 "The last statement included within a function must be a return statement"

Question

在下面的 SQL 函数中，我必须根据条件返回值，但它会引发错误。

"The last statement included within a function must be a return statement."

请帮助我克服这个问题。

ALTER FUNCTION [dbo].[GetBatchReleaseQuantity]   
(
@i_LocationID VARCHAR(50),
    @i_ProductID INT,
    @i_StartDate VARCHAR(50),  
    @i_EndDate VARCHAR(50),  
    @i_ProductInFlow int
)  
RETURNS numeric(18,3)  
 --WITH ENCRYPTION     
AS  
BEGIN  

  IF (@i_ProductInFlow ='2')
  BEGIN

  RETURN (SElECT ISNULL( SUM( BatchReleaseQuantity),0.00)  From  BatchReleaseDetails BRD
  LEFT OUTER JOIN BatchRelease BR ON BR.BatchReleaseID=BRD.BatchReleaseID
  Where  ProductId=@i_ProductID  AND LocationID=@i_LocationID AND BRD.CreatedOn>=convert(datetime,@i_StartDate+' 00:00:00') AND BRD.CreatedOn<=convert(datetime,@i_EndDate+' 23:59:59'))
  END
  ELSE
  BEGIN
  RETURN(SElECT ISNULL( SUM( AcceptedQuantity),0.00)  From  GoodsReceivedNoteDetail GRND
  LEFT OUTER JOIN GoodsReceivedNote GRN ON GRN.LocationID=@i_LocationID
  Where  ProductId=@i_ProductID  AND GRN.LocationID=@i_LocationID AND GRND.CreatedOn>=convert(datetime,@i_StartDate+' 00:00:00') AND GRND.CreatedOn<=convert(datetime,@i_EndDate+' 23:59:59'))
  END 
END

Answer 1

正如错误所暗示的，最后一个语句必须是 return 语句。与其他一些语言不同，IF/ELSE 的流程编译期间不检查语句，因此 SQL Server 不知道其中一个分支是强制性的(甚至 ELSE )。由于没有检查，因此无法知道函数是否会返回值，除非最后一条语句是 return 语句。即使是这样的简单函数也会失败:

CREATE FUNCTION dbo.FlowTest()
RETURNS INT
AS
BEGIN
    IF 1 = 1
    BEGIN
        RETURN 1;
    END
    ELSE
    BEGIN
        RETURN 0;
    END
END

解决方案是删除 ELSE :

CREATE FUNCTION dbo.FlowTest()
RETURNS INT
AS
BEGIN
    IF 1 = 1
    BEGIN
        RETURN 1;
    END
    -- ELSE REMOVED
    RETURN 0;

END

当 if 到达第一个 RETURN 时，该函数将停止执行。 ，所以 ELSE无论如何都不需要。

所以你的功能会变成:

ALTER FUNCTION [dbo].[GetBatchReleaseQuantity]   
(
@i_LocationID VARCHAR(50),
    @i_ProductID INT,
    @i_StartDate VARCHAR(50),  
    @i_EndDate VARCHAR(50),  
    @i_ProductInFlow int
)  
RETURNS numeric(18,3)  
 --WITH ENCRYPTION     
AS  
BEGIN  

  IF (@i_ProductInFlow ='2')
  BEGIN

    RETURN (SElECT  ISNULL( SUM( BatchReleaseQuantity),0.00)  
            FROM    BatchReleaseDetails BRD
                    LEFT OUTER JOIN BatchRelease BR 
                        ON BR.BatchReleaseID=BRD.BatchReleaseID
            WHERE   ProductId = @i_ProductID  
            AND     LocationID = @i_LocationID 
            AND     BRD.CreatedOn >= CONVERT(DATETIME, @i_StartDate+' 00:00:00') 
            AND     BRD.CreatedOn <= CONVERT(DATETIME,@i_EndDate + ' 23:59:59')
        )
  END

  RETURN (  SELECT  ISNULL( SUM( AcceptedQuantity),0.00)  
            FROM    GoodsReceivedNoteDetail GRND
                    LEFT OUTER JOIN GoodsReceivedNote GRN 
                        ON GRN.LocationID=@i_LocationID
            WHERE   ProductId = @i_ProductID  
            AND     GRN.LocationID = @i_LocationID 
            AND     GRND.CreatedOn >= CONVERT(DATETIME, @i_StartDate+' 00:00:00') 
            AND     GRND.CreatedOn <= CONVERT(DATETIME, @i_EndDate+' 23:59:59')
        )
  END 

END

不过，我看不出该函数的性能如何，以及为什么您将日期作为 varchar 传递，这超出了我的理解。你不关心在 23:59:59 到午夜之间创建的东西吗？

我倾向于将其重构为内联表值函数，并正确使用日期，例如

CREATE FUNCTION [dbo].[GetBatchReleaseQuantityTVP]   
(
    @i_LocationID VARCHAR(50),
    @i_ProductID INT,
    @i_StartDate DATE,  
    @i_EndDate DATE,  
    @i_ProductInFlow int
)  
RETURNS TABLE
 --WITH ENCRYPTION     
AS  
RETURN 
(   SElECT  ReturnValue = ISNULL( SUM( BatchReleaseQuantity),0.00)  
    FROM    BatchReleaseDetails BRD
            LEFT OUTER JOIN BatchRelease BR 
                ON BR.BatchReleaseID=BRD.BatchReleaseID
    WHERE   ProductId = @i_ProductID  
    AND     LocationID = @i_LocationID 
    AND     BRD.CreatedOn >= @i_StartDate
    AND     BRD.CreatedOn < DATEADD(DAY, 1, @i_EndDate)
    AND     @i_ProductInFlow ='2'
    UNION ALL
    SELECT  ISNULL(SUM( AcceptedQuantity),0.00)  
    FROM    GoodsReceivedNoteDetail GRND
            LEFT OUTER JOIN GoodsReceivedNote GRN 
                ON GRN.LocationID=@i_LocationID
    WHERE   ProductId = @i_ProductID  
    AND     GRN.LocationID = @i_LocationID 
    AND     GRND.CreatedOn >= @i_StartDate
    AND     GRND.CreatedOn < DATEADD(DAY, 1, @i_EndDate)
    AND     ISNULL(@i_ProductInFlow, '') != '2'
);

然后每当你调用 dbo.GetBatchReleaseQuantity(...)只需调用 (SELECT ReturnValue FROM dbo.GetBatchReleaseQuantityTVP(...)) .这将表现得更好，并且还将避免人们将无效日期传递给 varchar 参数。