Strange behavior with a scalar function that uses a table value function

Question

In a SQL Server 2022 database (compatibility level 160), I get this error when executing a scalar function:

Msg 245, Level 16, State 1, Line 22
Conversion failed when converting the varchar value 'PA' to data type int.

Code:

ALTER FUNCTION [dbo].[fnContractContainFlag]
    (@iContractNo int, 
     @FlagCode as char(10))
RETURNS BIT
AS
BEGIN
    DECLARE @bResult bit = 0,
            @cPattern CHAR(3) = '%,%'   

    IF PATINDEX(@cPattern, RTRIM(@FlagCode)) > 0 
    BEGIN
        IF EXISTS (SELECT tfl.pkTitleFlagID
                   FROM dbo.TitleFlag tfl 
                   INNER JOIN dbo.Title ttl ON (ttl.TitleID = tfl.TitleID)
                   INNER JOIN dbo.Flag flg ON (flg.FlagID = tfl.FlagID)
                   INNER JOIN dbo.fnSplit(RTRIM(@FlagCode), ',') tmp ON (tmp.VALOR = flg.pkFlagID)
                   WHERE ttl.ContractNo = @iContractNo 
        BEGIN
            SET @bResult = 1
        END
        ELSE
        BEGIN
            SET @bResult = 0
        END
    END
    ELSE
    BEGIN
        IF (EXISTS (SELECT f.FlagCode
                    FROM Title t 
                    INNER JOIN TitleFlag tf ON (tf.TitleID = t.TitleID)
                    INNER JOIN Flag f ON (f.FlagID = tf.FlagID)
                    WHERE t.ContractNo = @iContractNo 
                      AND RTRIM(f.FlagCode) = RTRIM(@FlagCode)))
        BEGIN
            SET @bResult = 1
        END
        ELSE
        BEGIN
            SET @bResult = 0
        END
   END

   RETURN (@bResult)
end

Note: dbo.fnSplit is a table-valued function.

When I ran the function

select dbo.fnContractContainFlag(308668,'PA')

runs fine.

When I ran

select dbo.fnContractContainFlag(318668, 'PA')

I get that error Msg 245...

Why the strange behaviour even when passing the same pattern in the parameter values?

What's wrong in my code?

If set INLINE = OFF in the function, the problem not show any more.

Regards.

In SQL Server 2012 the code described runs like a charm.

"If set `INLINE = OFF` in the function, the problem not show any more." - so that's your answer then. Likely one of the many bugs with UDF inlinng - may or may not be already fixed in a CU as you have provided no info about the build you are using https://support.microsoft.com/en-gb/topic/kb4538581-fix-scalar-udf-inlining-issues-in-sql-server-2022-and-2019-f52d3759-a8b7-a107-1ab9-7fbee264dd5d — Martin Smith, Aug 16 '23 at 04:56
RE: "dbo.fnSplit is a table-valued function" - you can use `STRING_SPLIT` in 2022 — Martin Smith, Aug 16 '23 at 04:58
My guess - without testing is that it might try and combine these `EXISTS` queries into a `UNION ALL` when inlining them and fall down as `tfl.pkTitleFlagID` has a different datatype than `f.FlagCode` - do you still see the issue if you project the same column in both the `EXISTS` ? — Martin Smith, Aug 16 '23 at 05:15
Thank you Charlieface, I forget to mention, we migrate from SQL Server 2012 to 2022 few week ago out DEV environment, we are rewrinting all the code with this behaviour, my concern is to understand why works with other values and fails with others. — Misael, Aug 16 '23 at 14:07
Thank you Martin Smith, as you mentioned the problem is the Inlining feature due the implementation, I rewrote the code using SELECT CASE instead IF...ELSE and the problem goes. — Misael, Aug 16 '23 at 14:12
Who knows, we can't see that function so we can't test it. Like I said, just use `STRING_SPLIT` or better use a Table Valued Parameter in the first place. — Charlieface, Aug 16 '23 at 16:45

Misael · Answer 1 · 2023-08-16T20:24:30.897

0

I rewrote the function the error not showing any more:

Code

edited Aug 16 '23 at 20:24

answered Aug 16 '23 at 18:50

Misael

1
1

Strange behavior with a scalar function that uses a table value function

1 Answers1