SQL Server 2022标量函数调用出现varchar转int失败异常问题

在SQL Server 2022（兼容级别160）中执行标量函数[dbo].[fnContractContainFlag]时，出现错误：

Msg 245, Level 16, State 1, Line 22 将varchar值'PA'转换为int数据类型失败。

函数代码如下：

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

注：dbo.fnSplit是自定义表值函数。

调用select dbo.fnContractContainFlag(308668,'PA')运行正常，但调用select dbo.fnContractContainFlag(318668, 'PA')时触发上述错误。该函数在SQL Server 2012中运行正常，设置INLINE=OFF后问题消失。

1. SQL Server 2022自动内联优化：兼容级别160（SQL Server 2022）默认会对符合条件的标量函数进行自动内联优化，将函数逻辑直接展开到调用查询中。SQL Server 2012无此特性，因此不会触发问题。
2. 执行计划异常分支执行：传入@FlagCode='PA'时，PATINDEX结果为0，理论上只会执行ELSE分支（单FlagCode匹配逻辑）。但内联优化后，查询优化器可能根据318668对应的数据分布生成了提前执行IF分支的计划，导致原本不会运行的代码被执行。
3. 隐式类型转换错误：IF分支中，fnSplit返回的tmp.VALOR是varchar类型（拆分字符串'PA'的结果），而flg.pkFlagID是int类型。等值匹配时SQL Server会隐式将'PA'转为int，转换失败触发错误。
4. 数据分布导致的计划差异：两个ContractNo对应的数据分布不同，优化器为它们生成了不同执行计划——308668的计划未触发IF分支，而318668的计划触发了，因此仅后者报错。

方案1：禁用自动内联 #

修改函数定义，添加WITH INLINE=OFF明确禁用内联，回到SQL Server 2012的执行逻辑：

ALTER FUNCTION [dbo].[fnContractContainFlag]
    (@iContractNo int, 
     @FlagCode as char(10))
RETURNS BIT
WITH INLINE=OFF
AS
BEGIN
    -- 原函数逻辑不变
END

方案2：修复隐式类型转换 #

将flg.pkFlagID显式转为varchar后再匹配，避免转换错误：

INNER JOIN dbo.fnSplit(RTRIM(@FlagCode), ',') tmp ON (tmp.VALOR = CAST(flg.pkFlagID AS VARCHAR(10)))

方案3：优化函数逻辑（推荐） #

用SQL Server 2022内置的STRING_SPLIT替代自定义fnSplit，同时明确分支条件，避免优化器误执行：

ALTER FUNCTION [dbo].[fnContractContainFlag]
    (@iContractNo int, 
     @FlagCode as char(10))
RETURNS BIT
AS
BEGIN
    DECLARE @bResult bit = 0,
            @trimmedFlagCode VARCHAR(10) = RTRIM(@FlagCode)

    IF @trimmedFlagCode LIKE '%,%'
    BEGIN
        IF EXISTS (
            SELECT 1
            FROM dbo.Title ttl
            JOIN dbo.TitleFlag tfl ON ttl.TitleID = tfl.TitleID
            JOIN dbo.Flag flg ON flg.FlagID = tfl.FlagID
            WHERE ttl.ContractNo = @iContractNo
              AND EXISTS (
                  SELECT 1
                  FROM STRING_SPLIT(@trimmedFlagCode, ',') s
                  WHERE s.value = CAST(flg.pkFlagID AS VARCHAR(10))
              )
        )
            SET @bResult = 1
    END
    ELSE
    BEGIN
        IF EXISTS (
            SELECT 1
            FROM dbo.Title t
            JOIN dbo.TitleFlag tf ON t.TitleID = tf.TitleID
            JOIN dbo.Flag f ON f.FlagID = tf.FlagID
            WHERE t.ContractNo = @iContractNo
              AND RTRIM(f.FlagCode) = @trimmedFlagCode
        )
            SET @bResult = 1
    END

    RETURN @bResult
END

方案4：改为内联表值函数（ITVF） #

重写为内联表值函数，既享受内联优化的性能，又避免分支执行异常：

CREATE OR ALTER FUNCTION [dbo].[fnContractContainFlag]
    (@iContractNo int, 
     @FlagCode as char(10))
RETURNS TABLE
AS
RETURN
(
    SELECT CASE
        WHEN EXISTS (
            SELECT 1
            FROM dbo.Title ttl
            JOIN dbo.TitleFlag tfl ON ttl.TitleID = tfl.TitleID
            JOIN dbo.Flag flg ON flg.FlagID = tfl.FlagID
            WHERE ttl.ContractNo = @iContractNo
              AND (
                  (RTRIM(@FlagCode) NOT LIKE '%,%' AND RTRIM(flg.FlagCode) = RTRIM(@FlagCode))
                  OR
                  (RTRIM(@FlagCode) LIKE '%,%' AND EXISTS (
                      SELECT 1
                      FROM STRING_SPLIT(RTRIM(@FlagCode), ',') s
                      WHERE s.value = CAST(flg.pkFlagID AS VARCHAR(10))
                  ))
              )
        ) THEN 1 ELSE 0 END AS Result
)

调用方式改为：SELECT Result FROM dbo.fnContractContainFlag(318668, 'PA')

内容的提问来源于stack exchange，提问作者Misael