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.
Best Answer
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 anINT
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:
With this:
If you also have to support older versions, you can do this (since we know
ISNUMERIC()
is not enough):A
CASE
expression is pretty reliable for forcing SQL Server to evaluate expressions in a particular order, but do note that there are exceptions.