What does two return statements inside an SQL function mean?

What code path is actually being returned from this a Scalar function that returns a decimal:

DECLARE @SafetyStockUnitsCalcNew decimal(14,2)  
   RETURN( SELECT STDEV(
     .
     .
     --  Calculating code here. 

     )

-- Return the result of the function
    RETURN @SafetyStockUnitsCalcNew 
END

So what gets returned?

Is it the first return scaler statement or the second scaler one?

Also, once @SafetyStockUnitsCalcNew is declared it is never used until it is returned. Where is it set?

The code inside STDEV does not reference @SafetyStockUnitsCalcNew at all.

Solution 1:

A function can have as many RETURN statements as you want.

E.g. Below there are three.

When the flow of control encounters a return statement then execution of the function stops and the value is returned.

CREATE FUNCTION dbo.Foo(@x int)
RETURNS INT
AS
BEGIN 
IF @x = 1
   RETURN 10;
ELSE
   RETURN 18;

RETURN 1/0;
END

SQL Server does not do anything like the same level of analysis as happens in C# to detect that all code paths return a value and warn you of unreachable code. It just insists that the last statement must be a return.

In the above example the third RETURN statement can never be reached but nonetheless without it you would see an error "The last statement included within a function must be a return statement.".

In your example the second RETURN statement can never be reached and it is not required for the "last statement" rule either so it is useless. Perhaps originally the SELECT assigned the result to the variable and then the person coding it realised that they could just return the SELECT result directly.

What does two return statements inside an SQL function mean?

Solution 1:

Related

Recent Posts