TSQL INLINE function failes in SQL Server 2019

Question

I have a function that executes with no problem when INLINE is turned off, but it hangs for ever when INLINE is turned on. Its a rather long function. Can someone please explain why this cannot execute INLINE? It takes 13 variables and formats them into a single string.

Thank you.

Use this to test the function:

select dbo.udf_FormatLocationText(
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N2SE4',
NULL
)

and this to create the function:

CREATE FUNCTION [dbo].[udf_FormatLocationText] 
(
@ST varchar(6),     
@County varchar(50),    
@Sec smallint,
@Twn smallint,
@TwnDir varchar(4),
@Rng smallint,
@RngDir varchar(4),
@Surv varchar(255),
@Abstract varchar(30),
@BkName  varchar(50),
@Addition  varchar(20),
@Qtr  varchar(100),
@TwnText  varchar(250) = NULL
)
RETURNS varchar(800)
WITH INLINE = OFF
AS
BEGIN
DECLARE
@STR varchar(15),
@Loc varchar(800),
@delim varchar(1),
@hasSurvAbst bit,
@hasBlkAdd bit

SET @hasSurvAbst = 0;
SET @hasBlkAdd = 0;

SET @STR = ''
SET @Loc = ''

-- 1st Get Twn vs Twn Text
IF (@Twn > 0)
    BEGIN
       SET @TwnText = RIGHT('000' + CAST(@Twn AS VARCHAR), 3)
    END


-- Twn / TwnDir
IF LEN(ISNULL(@TwnText,'')) > 0
    BEGIN      
       SET @STR = @TwnText

       -- Now add the TwnDir if it exists
       IF (LEN(@TwnDir) > 0 )
          BEGIN
             IF (LEN(@STR) = 0)
                BEGIN
                    SET @STR = @TwnDir
                END
             ELSE
                BEGIN
                    SET @STR = @STR +  @TwnDir
                END
          END
    END
ELSE
    BEGIN
       --  There's no Twn, but add the TwnDir if it exists
       IF (LEN(@TwnDir) > 0 )
          BEGIN
             IF (LEN(@STR) = 0)
                BEGIN
                    SET @STR = @TwnDir
                END
             ELSE
                BEGIN
                    SET @STR = @STR + '-' +  @TwnDir
                END
          END
    END


-- Rng / RngDir
IF (@Rng > 0)
    BEGIN
       IF (LEN(@STR) = 0)
          BEGIN
             SET @STR = RIGHT('00' + CAST(@Rng AS VARCHAR), 3)
          END
       ELSE
          BEGIN
             SET @STR = @STR + '-' +  RIGHT('00' + CAST(@Rng AS VARCHAR), 3)
          END

       -- Now add the RngDir if it exists
       IF (LEN(@RngDir) > 0 )
          BEGIN
             IF (LEN(@STR) = 0)
                BEGIN
                    SET @STR = @RngDir
                END
             ELSE
                BEGIN
                    SET @STR = @STR +  @RngDir
                END
          END
    END
ELSE
    BEGIN
       --  There's no Rng, but add the RngDir if it exists
       IF (LEN(@RngDir) > 0 )
          BEGIN
             IF (LEN(@STR) = 0)
                BEGIN
                    SET @STR = @RngDir
                END
             ELSE
                BEGIN
                    SET @STR = @STR + '-' +  @RngDir
                END
          END
    END

--Section
IF (@Sec > 0)
    BEGIN
       IF (LEN(@STR) = 0)
          BEGIN
             SET @STR = RIGHT('000' + CAST(@Sec AS VARCHAR), 3)
          END
       ELSE
          BEGIN
             SET @STR = @STR + '-' +  RIGHT('000' + CAST(@Sec AS VARCHAR), 3)
          END
    END

IF (Len(@STR)>0)
    BEGIN
       --Set the delimiter for STR
       SET @delim = ':'
    END

--Now Add Twn/Rng/Sec if we have anything
IF (Len(@STR)>0)
    BEGIN
       IF (LEN(@Loc)>0)
          BEGIN
             SET @Loc = @Loc + ', ' + @STR
          END
       ELSE
          BEGIN
             SET @Loc = @STR
          END
    END

--Quarter Call
IF LEN(ISNULL(@Qtr,'')) > 0 
    BEGIN
       IF LEN(@Loc) > 0
          BEGIN
             SET @Loc = @Loc + @delim + ' ' + @Qtr
          END
       ELSE
          BEGIN
             SET @Loc = @Qtr
          END
       SET @delim = ';'
    END

--SURVEY
IF LEN(ISNULL(@Surv,'')) > 0 
    BEGIN
       IF LEN(@Loc) > 0
          BEGIN
             SET @Loc = @Loc + @delim + ' ' + @Surv
          END
       ELSE
          BEGIN
             SET @Loc = @Surv
          END
       SET @delim = ','
       SET @hasSurvAbst = 1;
    END

--Abstract
IF LEN(ISNULL(@Abstract,'')) > 0 
    BEGIN
       IF LEN(@Loc) > 0
          BEGIN
             SET @Loc = @Loc + @delim + ' ' + @Abstract
          END
       ELSE
          BEGIN
             SET @Loc = @Abstract
          END
       SET @delim = ';'
       SET @hasSurvAbst = 1;
    END

IF (@hasSurvAbst = 1)
    BEGIN
       SET @delim = ';'
    END

--BLOCK
IF LEN(ISNULL(@BkName,'')) > 0 
    BEGIN
       IF LEN(@Loc) > 0
          BEGIN
             SET @Loc = @Loc + @delim + ' BLK-' + @BkName
          END
       ELSE
          BEGIN
             SET @Loc = @Loc + 'BLK-' + @BkName
          END
       SET @delim = ','
       SET @hasBlkAdd = 1;
    END

--ADDITION
IF LEN(ISNULL(@Addition,'')) > 0 
    BEGIN
       IF LEN(@Loc) > 0
          BEGIN
             SET @Loc = @Loc + @delim + ' ' + @Addition
          END
       ELSE
          BEGIN
             SET @Loc = @Addition
          END
       SET @delim = ';'
       SET @hasBlkAdd = 1;
    END
IF (@hasBlkAdd = 1)
    BEGIN
       SET @delim = ';'
    END

--County
IF (LEN(ISNULL(@County, '')) > 0)
    BEGIN
    IF (LEN(@Loc) = 0)
       BEGIN
          SET @Loc = @County
       END
    ELSE
       BEGIN
          SET @Loc = @Loc + @delim + ' ' + @County
       END
       SET @delim = ','
    END

--State
IF (LEN(ISNULL(@ST, '')) > 0)
    BEGIN
    IF (LEN(@Loc) = 0)
       BEGIN
          SET @Loc = @ST
       END
    ELSE
       BEGIN
          SET @Loc = @Loc + @delim + ' ' + @ST
       END
    END

RETURN  RTRIM(@Loc)
END

Answer 1

Ooops, I forgot to turn INLINE back "on" for this test example. Please change line #18 to this:
WITH INLINE = ON
Thanks.

Answer 2

Echoing what Guoxiong said: it works for me when I test on SQL 2019 CU7.

Make sure that you have the latest Cumulative Update installed. There have been several fixes for scalar-function inlining since the release of SQL 2019. Specifically, CU7 includes a fix where a long UDF could lead to hung schedulers, and the query never completing.

Answer 3

Hi @moondaddy ,

What is your version? You could run select @@version to check.

It is also working from my side. My version is SQL Server 2019 CU2.

I found one document FIX: Scalar UDF Inlining issues in SQL Server 2019 which may be helpful to you.

It mentioned that this CU includes several fixes across the following areas for scenarios in which a query that uses Scalar UDF Inlining may return an error or unexpected results:

• Type mismatch error if the return type of the UDF is a sql_variant (added in Microsoft SQL Server 2019 CU2)
• Uninitialized variables used in condition (IF-ELSE) statements cause errors (added in Microsoft SQL Server 2019 CU2)
• Explicit conversion error if UDF has SQL_VARIANT parameter (added in Microsoft SQL Server 2019 CU7)

After you upgrade to Microsoft SQL Server 2019 CU2 but before you upgrade to CU5, you should re-evaluate whether a UDF is eligible for inlining. To do this, refresh eligible scalar UDFs through either of the following methods:

• Run sp_refreshsqlmodule for applicable scalar UDFs。
• Alter or re-create the existing scalar UDF by using existing definition, permissions, and set properties.

Resolution:
Please upgrade to the latest cumulative updates for SQL Server:
Latest cumulative update for SQL Server 2019

You could also refer below link for more details about this issue.
SQL Server 2019 Fixes Function Inlining

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.