TSQL 函数中的奇怪行为（具有 int 变量或 NULL 的参数表现不同）？

我今天在使用 TSQL 函数时遇到了一个非常奇怪的问题。

该函数有几个参数（int 和 bit）。其中一个 (int) 被设置为 NULL 值。

在 params 函数中调用带有 NULL 的函数时返回 ERROR（错误是正常的，因为我尝试转换'%%'为int值）。当使用参数 @p1 调用该函数时，NULL该函数正在工作。

有人可以向我解释为什么它会这样吗？

我们正在使用：

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 
    Oct 19 2012 13:38:57 
    Copyright (c) Microsoft Corporation
    Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

我的代码是： 导致错误的代码：

((@p IS NOT NULL) AND (CONS.IDp = @p)) OR
((@p IS NULL) AND (UFCFD.IDUser IS NOT NULL) AND
 (CONS.IDp = CAST(UFCFD.Value1 AS INT))

备注：我编辑“导致错误”部分是因为我的同事进行了保护检查。我删除了那部分以显示函数的原始状态（来源是：(CONS.IDp = CASE WHEN ISNUMERIC(UFCFD.Value1) = 1 THEN CAST(UFCFD.Value1 AS INT) ELSE NULL END))

导致错误的调用：

SELECT * FROM dbo.myFunc(NULL); 

有效的电话：

DECLARE @p INT = NULL;
SELECT * FROM dbo.myFunc(@p); 

PS 如果问题不清楚，请通过评论告诉我。

编辑2：

这是函数的全文：

CREATE FUNCTION dbo.myFunc
(   
    @IDUser INT, 
    @p INT,
    @IDGrid INT,
    @IsWithLead BIT,
    @IDLeadValueResource INT,
    @IDLanguage INT,
    @IsAllPresent BIT,
    @IDAllValueResource INT
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT
        -99999999 AS ID,
        dbo.funs_GetResourceText(@IDLanguage,@IDAllValueResource) AS [Text],
        -99999999 AS Sort
    WHERE (@IsAllPresent = 1)

    UNION ALL

    SELECT
        0 AS ID,
        dbo.funs_GetResourceText(@IDLanguage,@IDLeadValueResource) AS [Text],
        0 AS Sort
    WHERE (@IsWithLead = 1)

    UNION ALL

    SELECT 
        CONS.IDConsumerType AS ID,
        CASE
            WHEN @IDLanguage = 40001 THEN CONS.ConsumerType_Name_en  
            WHEN @IDLanguage = 40002 THEN CONS.ConsumerType_Name_it 
            WHEN @IDLanguage = 40003 THEN CONS.ConsumerType_Name_de 
            WHEN @IDLanguage = 40004 THEN CONS.ConsumerType_Name_fr 
            ELSE CONS.ConsumerType_Name_en END AS [Text],
        ROW_NUMBER() OVER(ORDER BY CONS.SegmentOrder ASC) AS Sort
    FROM 
        dbo.V_ConsumerTypes AS CONS
        LEFT JOIN dbo.Grids GRD ON (GRD.ID = @IDGrid)
        LEFT JOIN dbo.CONFieldCollection_FieldDefinition FCFD ON
            (FCFD.IDFieldCollection = GRD.IDGridFieldCollection) AND
            (FCFD.FieldName LIKE '%IDp%')
        LEFT JOIN dbo.CONUser_FieldCollection_FieldDefinition UFCFD ON
            (UFCFD.IDFieldCollection = GRD.IDGridFieldCollection) AND
            (UFCFD.IDUser = @IDUser) AND
            (UFCFD.IDFieldCollections_FieldDefinitions = FCFD.ID) 
    WHERE
        ((@p IS NOT NULL) AND (CONS.IDp = @p)) OR
        ((@p IS NULL) AND (UFCFD.IDUser IS NOT NULL) AND
         (CONS.IDp = CAST(UFCFD.Value1 AS INT))
)

有效的电话是：

DECLARE @IDUser INT = -100;
DECLARE @p INT = NULL;
DECLARE @IDGrid INT = 17;
DECLARE @IsWithLead BIT = 0;
DECLARE @IDLeadValueResource INT = 0;
DECLARE @IDLanguage INT = 40002;
DECLARE @IsAllPresent BIT = 1
DECLARE @IDAllValueResource INT = -177;

DECLARE @dt DATETIME = GetDate();
SELECT * FROM dbo.fun_ClROME_MdFillComboConsumerTypes(@IDUser, @p, @IDGrid, @IsWithLead, @IDLeadValueResource,
    @IDLanguage, @IsAllPresent, @IDAllValueResource)
ORDER BY Sort

如果我们使用NULL而不是@p然后我收到错误，因为我尝试转换'%%'为int- 这是正确的。

问题是显然函数的行为不同，这是一个大问题。