|
|
Error Message:
Server: Msg 455, Level 16, State 2,
Procedure Function Name, Line 65535
The last statement included within a function
must be a return statement.
Causes:
As the error message suggests, the last statement in a function must be a RETURN
statement. Even if the execution path of the statements in a function
will execute a RETURN statement, the error will still be encountered.
|
To illustrate, here’s a user-defined function that returns the smaller number
between two integer parameters:
CREATE FUNCTION [dbo].[ufn_Least]
( @pInt1 INT, @pInt2 INT )
RETURNS INT
AS
BEGIN
IF @pInt1 > @pInt2
RETURN @pInt2
ELSE
RETURN @pInt1
END
GO
Even if one of the RETURN statements will be executed given the condition, but
since the last statement is not a RETURN statement by itself and not within a
condition, the following error will be raised:
Server: Msg 455, Level 16, State 2, Procedure ufn_Least, Line 65535
The last statement included within a function must be a return statement.
Solution/Workaround:
To avoid this error, make sure that the last statement in your user-defined
function is the RETURN statement. In the case of the user-defined
function shown above, here’s an updated version of the function that gets rid
of the error:
CREATE FUNCTION [dbo].[ufn_Least]
( @pInt1 INT, @pInt2 INT )
RETURNS INT
AS
BEGIN
IF @pInt1 > @pInt2
RETURN @pInt2
RETURN @pInt1
END
GO
Instead of putting the last RETURN statement inside the ELSE statement, it is
executed by itself and the function still produces the same result.
|