Return statement inside If condition does not work in SQL Functions

Question

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?

You can try this SELECT dbo.[Dyve_FN_TryConvertInt] ('63d9efcd') — Sunny Drall, Apr 08 '20 at 09:35
Install [Cumulative Update 4](https://support.microsoft.com/help/4548597/cumulative-update-4-for-sql-server-2019), it [fixes some bugs with scalar UDF inlining](https://support.microsoft.com/help/4538581/fix-scalar-udf-inlining-issues-in-sql-server-2019) and it may well fix this one. Failing that, you can use `WITH INLINE = OFF` to turn off the inlining. — Jeroen Mostert, Apr 08 '20 at 09:40
but anyway why aren't you using the built in try convert or try_parse function? — Martin Smith, Apr 08 '20 at 09:41
What's wrong with `TRY_CONVERT`? Why reinvent the wheel? `ISNUMERIC` is also a terrible function, it has so many false positives and negatives. `ISNUMERIC('.')` returns `1`, yet `CONVERT(int,'.')` will fail. `ISNUMERIC('')` will return `0` let `CONVERT(int,'')` will succeed (and there are plenty of other examples). — Thom A, Apr 08 '20 at 09:45
TRY_CONVERT is definitely a better approach, thanks for pointing out. It fixed the issue. Also, turning off inlining fixed the issue with WITH INLINE = OFF. It took me hours to figure where the actual issue is. Thank you everyone. Please feel free to post it as an answer, I'll upvote. — Sunny Drall, Apr 08 '20 at 09:57
Cumulative Update 4 did not fix the issue fyi, I restarted my PC after this update and still the same. — Sunny Drall, Apr 08 '20 at 10:30
I have experienced exactly same issue. To isolate the reason, i had wasted 3 hours!. FYI my SQL Server version is 15.0.2000.5.. — jelongpark, Sep 19 '20 at 16:41

score 4 · Accepted Answer · answered Apr 10 '20 at 17:25

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

The execution plan looks as follows

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)

I've got same error. my sql server version is SQL 2019 Standard edition, version number is 15.0.2000.xx. — jelongpark, Sep 19 '20 at 16:29

Return statement inside If condition does not work in SQL Functions

1 Answers1