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.

• Aaron Bertrand: So I'm lost - you were getting an error based on the @p parameter, depending on whether you passed an explicit NULL or assigned NULL to a variable, when @p wasn't even in the query? Can you reproduce using a simpler function that we have a hope of replicating?
• Bogdan Bogdanov: Sorry, @Aaron Bertrand that I did not explain well. When I run the function using explicit NULL I get error. I suppose that I try to make ` CAST(UFCFD.Value1 AS INT)` but the value of UFCFD.Value1 is '%%' which normally give me error. When I use @p to which is assigned NULL value the function returns results. This leads me to conclusion that the logic is not follwing the same path. I my view in that case the same code from above is not processed so I don't get that error. I will try to simplify the function to get the same issue, but for me whole situation is very strange!
• Aaron Bertrand: You may have different plans when using a literal vs. a variable. Again, can you demonstrate this on a simpler function? I can't try to replicate what you have because I don't have all the dependencies (all your tables and the other functions). Also, what is the actual error? All that text and you never actually posted the error message...
• Bogdan Bogdanov: I try to simplify. The error text is: Msg 245, Level 16, State 1, Line 11 Conversion failed when converting the nvarchar value '%%' to data type int.. The error is reaised because in the table dbo.CONUser_FieldCollection_FieldDefinition UFCFD the value of field UFCFD.Value1 is equal to '%%'. I try to CAST it to INT inside (CONS.IDp = CAST(UFCFD.Value1 AS INT)). That is the reason for ERROR. The problem is that this condition is triggered only when I pass NULL to function (not param with value NULL). When I pass @p = NULL it works which means I did not evaluate that CAST.

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.

• Bogdan Bogdanov: We already do that, @Aaron Bertrand (see REMARK). I ask because I want to be sure why I got inside that kind of issue. I will use the new command TRY_CONVERT (and will read the links that you post me, obviously there are specific details with ISNUMERIC() and CAST()). 10x for help.
• Aaron Bertrand: Understood, but the question doesn't really describe why that is "a protection check" so this information may be useful to other readers. Also, please tell your colleague to stop trusting ISNUMERIC(), as it is not reliable: SELECT CASE WHEN ISNUMERIC('2e4') = 1 THEN CAST('2e4' AS INT) END;
• Bogdan Bogdanov: Sure, @Aaron Bertrand. 10x again for detailed answer.