Possible bug in SQL Server 2019

Question

We sometimes create "super insert sprocs" that call other insert sprocs. These "other" insert sprocs often return the Id of of the record they just inserted, but in the case of super insert sprocs, we return the Id of the main (top level) record being added (i.e. Person) and not the Id of a related value added to the record by a separate sproc (i.e. the Id of a telephone number). So we have to swallow the Id of the inner sprocs in a temp table.

The following script (a basic example of what we do) correctly raises an error when run on Microsoft SQL Server 2017...

...but it does NOT raise an error when run on Microsoft SQL Server 2019.

CREATE PROCEDURE ip_MyProcedure
AS
SELECT 1;
GO

CREATE TABLE
  #MyTable (
    Id int IDENTITY (1, 1) NOT NULL,
	RecordId int NULL
  );

INSERT INTO
  #MyTable (
    Id
  )
EXECUTE
  ip_MyProcedure;

DROP TABLE #MyTable;
GO

DROP PROCEDURE ip_MyProcedure;
GO

However, if the value inserted is changed to a SELECT rather than an EXECUTE, it correctly raises an identity exception in SQL2019.

Answer 1

UPDATE. I tried on this version

Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64) 
	Apr  1 2023 12:10:46 
	Copyright (C) 2019 Microsoft Corporation
	Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )

and it did generate a correct error.

Answer 2

I cannot reproduce the error on SQL Server 2019 CU 15 or SQL Server 2019 CU 21. I suggest you install the latest CU for SQL Server 2019, which is CU 21 as of this writing.