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.
@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?NULL
I get error. I suppose that I try to make ` CAST(UFCFD.Value1 AS INT)` but the value ofUFCFD.Value1
is'%%'
which normally give me error. When I use@p
to which is assignedNULL
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!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 tabledbo.CONUser_FieldCollection_FieldDefinition UFCFD
the value of fieldUFCFD.Value1
is equal to'%%'
. I try toCAST
it toINT
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 passNULL
to function (not param with valueNULL
). When I pass@p = NULL
it works which means I did not evaluate thatCAST
.