Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : SQL Server Tutorial - UPDATE Statement 101
Error Messages
Home > SQL Server Error Messages > Msg 455 - The last statement included within a function must be a return statement.
SQL Server Error Messages - Msg 455 - The last statement included within a function must be a return statement.

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.

Related Articles :