Return statement inside If condition does not work in SQL Functions

user_3422199 0
Viewed 547

I have following function to convert string to int if convertible:

CREATE FUNCTION dbo.[Dyve_FN_TryConvertInt](@Value varchar(18))
RETURNS int
AS
BEGIN
    SET @Value = REPLACE(@Value, ',', '')
    IF ISNUMERIC(@Value + 'e0') = 0 RETURN NULL
    IF ( CHARINDEX('.', @Value) > 0 AND CONVERT(bigint, PARSENAME(@Value, 1)) <> 0 ) RETURN NULL
    DECLARE @I bigint =
        CASE
        WHEN CHARINDEX('.', @Value) > 0 THEN CONVERT(bigint, PARSENAME(@Value, 2))
        ELSE CONVERT(bigint, @Value)
        END
    IF ABS(@I) > 2147483647 RETURN NULL
    RETURN @I
END
GO

It worked before and after upgrading it to the following version

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 18362: ) (Hypervisor)

The return statement stopped returning the null value if it is not numeric. Therefore I started getting the following error:

Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar
 to bigint.

I have to add a return statement outside of If condition to return any value out of the function. How can I fix this?

enter image description here

sql-server-2019 user-defined-functions sql-server
1 Answers

This is a bug with the inlining of Scalar UDFs in SQL Server 2019.

The execution plan looks as follows

enter image description here

The value passed to the function is emitted from the constant scan in the top right and aliased as Expr1000. This is passed along until eventually it gets to the compute scalar that contains the following expression

[Expr1007]=CASE
           WHEN CHARINDEX('.', [Expr1000]) > ( 0 )
             THEN CONVERT(BIGINT, PARSENAME(CONVERT_IMPLICIT(nvarchar(18), [Expr1000], 0), ( 2 )), 0)
           ELSE CONVERT(BIGINT, [Expr1000], 0)
         END

In the case the input is not remotely numeric this will blow up regardless of which path the CASE expression ends up taking.

The issue looks similar to the one here. The green highlighted constant scan is under an inner join with a pass through predicate so would only be evaluated if the ISNUMERIC test was true. The output list for that constant scan is empty so I assume that likely the expression got moved from there with the same faulty logic as the linked answer.

You can use WITH INLINE = OFF to turn off inlining for that function but likely you should just remove it entirely and replace with TRY_CONVERT (or at least replace the body of the function with that if it is not possible to remove it entirely at this time)

user_732260
Related