Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?

Question

I experienced very strange problem today with TSQL function.

The function has several parameters (int and bit). One of the (int) is set with value NULL.

When calling a function with NULL inside the params function return ERROR (the error is ok, because I try to convert '%%' to int value). When the function is called with param @p1 which value is NULL the function is working.

Can someone explain to me why it works like that?

We are using:

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)

MY CODE IS: The code that causes error:

((@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))

REMARK: I edit "causing error" part because my colleague put a protection check. I remove that part to show the original state of the function (The source was: (CONS.IDp = CASE WHEN ISNUMERIC(UFCFD.Value1) = 1 THEN CAST(UFCFD.Value1 AS INT) ELSE NULL END))

The call that causes error:

SELECT * FROM dbo.myFunc(NULL); 

The call that works:

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

P.S. If the question is unclear please let me know via comment.

EDIT2:

This is the full text of the function:

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))
)

The call that works is:

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

If we use NULL instead of @p then I receive error, because I try to convert '%%' to int - this is correct.

The problem is that obviously function behaves differenly which is a big issue.

Answer 1

I think you're trying to solve for the wrong problem. As I suggested above, I think you're getting different results simply because you currently have different plans (one for the literal and one for the variable). The plan you're currently getting that causes an error is attempting the cast before the rows are filtered out, but both methods could cause an error if the plan that currently "works fine" gets recompiled.

What you should be doing is using a CASE expression to ensure that only numeric values that can be cast to an INT are actually attempted to be converted. You need to control this explicitly because you can't always rely on SQL Server filtering rows before attempting calculations.

In 2012 and up, you can simply replace this:

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

With this:

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

If you also have to support older versions, you can do this (since we know ISNUMERIC() is not enough):

(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)

A CASE expression is pretty reliable for forcing SQL Server to evaluate expressions in a particular order, but do note that there are exceptions.