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而不是@pthen 我收到錯誤，因為我嘗試轉換'%%'為int- 這是正確的。

問題是顯然函式的行為不同，這是一個大問題。

我認為您正在嘗試解決錯誤的問題。正如我上面建議的那樣，我認為你得到不同的結果僅僅是因為你目前有不同的計劃（一個用於文字，一個用於變數）。您目前獲得的導致錯誤的計劃是在過濾掉行之前嘗試進行強制轉換，但是如果重新編譯目前“工作正常”的計劃，這兩種方法都可能導致錯誤。

您應該做的是使用CASE表達式來確保只有可以轉換為 an 的數值才會INT被實際嘗試轉換。您需要明確控制這一點，因為在嘗試計算之前您不能總是依賴 SQL Server 篩選行。

在 2012 年及以後，您可以簡單地替換它：

(CONS.IDp = CAST(UFCFD.Value1 AS INT))

有了這個：

(CONS.IDp = TRY_CONVERT(INT, UFCFD.Value1))

如果您還必須支持舊版本，您可以這樣做（因為我們知道ISNUMERIC()還不夠）：

(CONS.IDp = CASE 
 WHEN UFCFD.Value1 NOT LIKE '%[^0-9]%' 
 AND 
 (
   LEN(UFCFD.Value1 < 11 
   OR
   (
     LEN(UFCFD.Value1) = 11 
     AND LEFT(UFCFD.Value1,1) IN ('0','1','2')
   )
 ) THEN CONVERT(INT, UFCFD.Value1) END)

表達式對於CASE強制 SQL Server 以特定順序計算表達式非常可靠，但請注意存在異常。

引用自：https://dba.stackexchange.com/questions/108131