Sql 函数问题“包含在函数中的最后一条语句必须是返回语句"； [英] Sql Function Issue "The last statement included within a function must be a return statement"

问题描述

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

In the below SQL function I have to return value based on condition but it throws a error.

"包含在函数中的最后一条语句必须是返回声明."

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

请帮助我解决这个问题.

Pls help me to overcome this issue.

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

推荐答案

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

As the error suggests, the last statement must be a return statement. Unlike some other languages the flow of the IF/ELSE statement is not checked during compilation, so SQL Server is not aware that one of the branches is mandatory (even ELSE). Since this is not checked there is no way of knowing whether the function will return a value unless the last statement is a return statement. Even a simple function like this will fail:

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

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

The function will stop execution when if reaches the first RETURN, so the ELSE is not required anyway.

所以你的函数会变成:

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 到午夜之间创建的东西吗?

I can't see how the function will perform well though, and why you are passing a date as a varchar is beyond me. Do you not care about things that were created between 23:59:59 and midnight?

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

I would be inclined to refactor this as an inline tablevalued function, and use dates properly, e.g.

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 参数.

Then whenever you would call dbo.GetBatchReleaseQuantity(...) simply call (SELECT ReturnValue FROM dbo.GetBatchReleaseQuantityTVP(...)). This will perform significantly better, and will also avoid people passing invalid dates to a varchar parameter.

这篇关于Sql 函数问题“包含在函数中的最后一条语句必须是返回语句"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！