Think about the next repro instance (fiddle):
CREATE FUNCTION dbo.Repro (@myYear int) RETURNS datetime AS BEGIN IF @myYear <> 1990 BEGIN RETURN NULL END DECLARE @firstOfYear datetime; SET @firstOfYear = DATEFROMPARTS(@myYear, 1, 1); IF DATEDIFF(day, @firstOfYear, @firstOfYear) <> 0 BEGIN RETURN NULL END RETURN @firstOfYear END SELECT dbo.Repro(0);
Clearly, that perform ought to return the primary of January 1990 if the enter is
NULL in any other case. Sure, I do know that
DATEDIFF(day, @firstOfYear, @firstOfYear) <> 0 is a nonsensical operation. This can be a mcve to exhibit a possible bug, not manufacturing code.
Now let’s execute
SELECT dbo.Repro(0) on SQL Server 2017 and SQL Server 2019.
Precise consequence on SQL Server 2017:
Precise consequence on SQL Server 2019:
Msg 289 Degree 16 State 1 Line 1
Can’t assemble information sort date, a number of the arguments have values which aren’t legitimate.
Apparently, SQL Server 2019 executes a number of the code beneath the preliminary guard clause (
IF @myYear <> 1990) even when it should not.
- Is that this anticipated habits, or did I discover a bug in SQL Server 2019?
- If that is anticipated habits, how do I appropriately write a guard clause validating enter paramters?