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

 Msg 455, Level 16, State 2, Line 1
The last statement included within a function must be a return statement.

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 understand better, 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

Output:
Msg 455, Level 16, State 2, Procedure ufn_Least, Line 8 [Batch Start Line 0]
The last statement included within a function must be a return statement.

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.

Case expressions may only be nested to level %d

Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level %d.

I never experienced this in SQL Server 2017 onwards, but this was being experienced in previous version on Linked Server only. So below example is the demonstration of that assuming linked server.

Declare @id as int
set @id=13
SELECT 
    (CASE @Id
        WHEN 1 THEN 1
        WHEN 2 THEN 2
        WHEN 3 THEN 3
        WHEN 4 THEN 4
        WHEN 5 THEN 5
        WHEN 6 THEN 6
        WHEN 7 THEN 7
        WHEN 8 THEN 8
        WHEN 9 THEN 9
        WHEN 10 THEN 10
        WHEN 11 THEN 11
	WHEN 12 THEN 12
        WHEN 13 THEN 13
        WHEN 14 THEN 14
    END) AS Test
	from [SANTANA\MSSQLSERVER17].Rohit.dbo.Emp

You can resolve this by using below technique.

Using multiple coalesce cases.

Using Open query.

Points to remember :

• The above query will work absolutely fine if you run from the local instances even more than 10 conditions.this error message only happens when we are applying a case from data that comes from a Linked Server.
• If the query does not use Linked Server, you do not need to use this technique. You can use as many conditions as you want in the same case.
• If your query uses Linked Server that points to its own instance, you do not need to use this technique either. It works like a normal query without linked server.
• The COALESCE function accepts multiple conditions, so it is not limited to just 2 cases, they can be multiple (although the complexity of the code is increasing.)
• You can only use up to 9 conditions in each CASE in scenarios where the query is done on remote data.
• Using ELSE NULL at the end of each case is optional.
• Subquery and CTE do not resolve this issue.
• OPENQUERY and OPENROWSET do not have this limitation of 10 CASE conditions either.