Consider the following repro example ([fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a7d81b741dbe78aa6fb94b1edec1d78a)):
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);
Obviously, that function should return the first of January 1990 if the input is `1990`, and `NULL` otherwise. Yes, I know that `DATEDIFF(day, @firstOfYear, @firstOfYear) <> 0` is a nonsensical operation. This is a [mcve](https://stackoverflow.com/help/minimal-reproducible-example) to demonstrate a potential bug, not production code.
Now let's execute `SELECT dbo.Repro(0)` on SQL Server 2017 and SQL Server 2019.
**Expected result**: `NULL`.
**Actual result on SQL Server 2017**: `NULL`
**Actual result on SQL Server 2019**:
> Msg 289 Level 16 State 1 Line 1
> Cannot construct data type date, some of the arguments have values which are not valid.
Apparently, SQL Server 2019 executes some of the code below the initial guard clause (`IF @myYear <> 1990`) even if it shouldn't.
My questions:
* Is this expected behavior, or did I find a bug in SQL Server 2019?
* If this is expected behavior, how do I correctly write a guard clause validating input paramters?
TopAnswers
Databases
Top Answer
Martin Smith (imported from SE)
This is a bug with the inlining of Scalar UDFs (or perhaps a bug with the query optimiser that is being exposed more by scalar UDF inlining). You can use `WITH INLINE = OFF` to turn off inlining for that function.
Using a variable instead of a constant shows a bit more detail
declare @myYear int = 0
SELECT dbo.Repro(@myYear);
[![enter image description here][1]][1]
- Node 5 defines `Expr1000 = CASE WHEN [@myYear]<>(1990) THEN (1) ELSE (0) END`
- Node 2 defines `[Expr1003] = Scalar Operator(CONVERT_IMPLICIT(datetime,datefromparts([@myYear],(1),(1)),0))`
These expressions are simplified when using the literal `0` to `1` and `CONVERT_IMPLICIT(datetime,datefromparts((0),(1),(1)),0)` respectively.
The `datefromparts(0` will throw the error when evaluated.
- Node 6 defines `Expr1002 = CASE WHEN [Expr1000] = (1) THEN (1) ELSE (0) END`
And `Expr1002` is used as a [passthru predicate][2] on a nested loops join (node 3). On the inside of that nested loops the constant scan (node 7) returns no columns.
So this looks like the same basic issue as
@@@ answer 199
where an expression on the inside of a nested loops protected by a passthru predicate is moved out into an unprotected region.
---
Conor Cunningham MSFT [replied](https://dba.stackexchange.com/questions/265912/sql-server-2019-executes-unreachable-code?noredirect=1&lq=1#comment523590_265917):
> Microsoft is aware and a fix should be available in the next Cumulative Update. Thanks for reporting it, and sorry you hit this issue.
[1]: https://i.stack.imgur.com/XKlAk.png
[2]: https://docs.microsoft.com/en-gb/archive/blogs/craigfr/subqueries-in-case-expressions