|
|
Error Message
Server: Msg 455, Level 16, State 2, Line 1
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 / Work Around:
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.
|