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
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.
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
int - this is correct.
The problem is that obviously function behaves differenly which is a big issue.