sql-serveruser-defined-functions

How to call SQL user function from another one, only if it exists on SQL Server 2022?


Problem:

SQL server 2022 nondeterministically throws

Cannot find either column "ext" or the user-defined function or aggregate "ext.cfn_Something", or the name is ambiguous.

in a situation, where it should not attempt to invoke that function. It used to work fine in SQL 2016, problem started to appear after upgrade to 2022.

Context:

Let's have scalar user-defined function ext.cfn_Something(@ID INT) RETURNS BIT, and this function does NOT exist. It is designed to override default function only when it exists on the target server.

And then, we have the actual dbo.cfn_Something(@ID INT) RETURNS BIT that exists and does simple logic:

  • Check if the ext version exist on this server. And use it if it does.
  • Otherwise do some default trivial calculation.
CREATE FUNCTION dbo.cfn_Something(@ID INT) RETURNS BIT AS 
BEGIN
  DECLARE @Ret BIT
  IF OBJECT_ID('ext.cfn_Something') IS NOT NULL
    SET @Ret=ext.cfn_Something(@ID)
  ELSE BEGIN
    SET @Ret=CASE WHEN @ID>0 THEN 1 ELSE 0 END
  END
  RETURN @Ret
END

This function is called from a stored procedure

CREATE PROCEDURE dbo.csp_Something @SomeColumn INT
AS
SELECT ID, dbo.cfn_Something(ID) AS Value FROM Table WHERE SomeColumn=@SomeColumn

Observations:

  • It seems to appear randomly.
  • It seems to disappear after adding WITH RECOMPUTE to the stored procedure.
  • It seems to disappear for a while after running the procedure from admin account.

I suspect it has something to do with caching execution plan for the procedure, or the function itself. And maybe one scenario (rebuild vs. reuse) is happier than the other.

Questions:

  • Are you aware of functional change in 2022 around execution that could cause this?
  • Would WITH RECOMPILE be a stable solution?
  • Is there other WITH option available that could solve this?
  • Is there a better way of solving the original problem, while dbo wouldn't change, and instalations are free to create any objects in ext schema?
  • Is there a way to diagnose the execution vs. caching?

Thank you

I couldn't find similar problem online. I tried adding the WITH RECOMPILE that seems to work for now.


Solution

  • This appears to be an inlining issue (the new Scalar UDF Inlining feature). You can see this in action in this fiddle.

    To be honest, the real answer is that you shouldn't rely on statements not being executed. SQL Server compiles full batches before executing them, and it's only because of deferred compilation that this ever worked in the first place.


    • A better solution might be to always define the function, and have the override just rewrite the existing function.

    • Alternatively, you could use synonyms. For example, create one default function, and have a synonym which can be changed to refer to a different one.

    CREATE FUNCTION dbo.cfn_Something_Default(@ID INT)
    RETURNS BIT
    WITH SCHEMABINDING
    AS
    BEGIN
        RETURN IIF(@ID > 0, CAST(1 AS bit), CAST(0 AS bit));
    END;
    
    CREATE SYNONYM dbo.cfn_Something FOR dbo.cfn_Something_Default;
    

    And change it:

    DROP SYNONYM dbo.cfn_Something;
    
    CREATE SYNONYM dbo.cfn_Something FOR ext.cfn_Something;