מרץ17
Written by:
ronen ariely
17/03/2021 21:29 
background
This issue was reported in the Microsoft QnA forum! Using nested User-defined Scalar Functions in SQL Server version 15.0.4102.2 returns an error. I confirmed this issue in several machines.
This issue was first (as much as I know this was first) reported by Stefan in this thread:
https://docs.microsoft.com/en-us/answers/questions/318695/sql-server-2019-cu9-scalar-udf-inlining-issue.html
Reproduce the issue the issue
Let's create 3 nested Inline User-defined Scalar Functions
CREATE FUNCTION [dbo].[GetSub1] (@param NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
RETURN (@param + ' Test2')
END
GO
CREATE FUNCTION [dbo].[GetSub2] ()
RETURNS NVARCHAR(4000)
AS
BEGIN
RETURN (
SELECT TOP 1 name
FROM [sys].[columns]
)
END
GO
CREATE FUNCTION [dbo].[GetMain1] ()
RETURNS NVARCHAR(4000)
AS
BEGIN
RETURN (dbo.GetSub1(dbo.GetSub2()))
END
GO
Now when we execute the GetMain1 function if you are using the last (at this time) SQL Server version which is 15.0.4102.2 then you will get an error
SELECT [dbo].[GetMain1] ()
GO
Msg 596, Level 21, State 1, Line 29
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 29
A severe error occurred on the current command. The results, if any, should be discarded.
Note! This issue does not exists in previous version 15.0.4083.2
Tests and confirmation
First I tested on production machine which had a working instance version CU9 (15.0.4102.2) and I confirm that I get the same issue.
Secondly I started with a machine which include multiple versions of SQL Server instances from 2014 to 2019. The instance of 2019 was CU8 (15.0.4083.2). No issue appeared. I updated the server to the last version and the issue raised.
Note! Since this appeared in the last version and usually Azure SQL Database uses the latest version, then I decided to test in Azure SQL Database and I can confirm that this is NOT an issue in the Azure. This issue exists only in SQL Server on-premises version CU9 (15.0.4102.2)
Work around
This issue is definitely related to to
If you have to use this version and you must use nested inline User-defined Scalar Functions, then you should NOT use inline functionality.
Option 1: Disable the inline functionality of the function using "WITH INLINE = OFF"
Option 2: Instead of using inline function use multi statement function
Obviously this mean that we lose the new advantages in using inline User-defined Scalar Functions, which I love so much.
What Next?
I sent email to one of the SQL Server developers and I asked the user in the fogram to open the official bug report so he will get the credit on reporting this first. I publish this issue here for the sake of my clients and readers, but again, this issue was not reported by me. I only helped to confirm it :-)
I will provide more information once I will get...