SQL Server: Cannot execute existing scalar function from within table-valued function

Question

I created this scalar function, without any problems.
But I can't execute it from another function (inline table-valued).

USE [test]
GO

/****** Object:  UserDefinedFunction [dbo].[NameFromEnumerationID]    Script Date: 10/9/2018 6:46:22 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[NameFromEnumerationID]
(
    @EnumerationIDParam INT NOT NULL
)
RETURNS NVARCHAR ( 48 )
WITH    NATIVE_COMPILATION ,
        SCHEMABINDING
AS BEGIN ATOMIC WITH (  TRANSACTION ISOLATION LEVEL = SNAPSHOT      ,
                        LANGUAGE                    = N'English'    )
    RETURN CHOOSE ( @EnumerationIDParam                         ,
                    CAST ( N'ExampleName1' AS NVARCHAR ( 48 ) ) ,
                    CAST ( N'ExampleName2' AS NVARCHAR ( 48 ) ) ,
                    CAST ( N'ExampleName3' AS NVARCHAR ( 48 ) ) ,
                    CAST ( N'ExampleName4' AS NVARCHAR ( 48 ) ) ,
                    CAST ( N'ExampleName5' AS NVARCHAR ( 48 ) ) ,
                    CAST ( N'ExampleName6' AS NVARCHAR ( 48 ) ) ) ;
END
GO

When I try to create another function that executes the above function, I receive the error:
'NameFromEnumerationID' is not a recognized built-in function name.

CREATE FUNCTION [dbo].[NamesFromEnumerationIDs]
(
    @EnumerationIDListParam [IntList] NOT NULL READONLY
)
RETURNS TABLE AS RETURN
(
    SELECT  [Value]                                 AS [ID]                     ,
/* ERROR */ NameFromEnumerationID   ( [Value] )     AS [EnumerationName]        ,
    UPPER ( [NameFromEnumerationID] ( [Value] ) )   AS [EnumerationNameUpper]   ,
            [IsEnumerationIDValid]  ( [Value] )     AS [IsValid]
        FROM @EnumerationIDListParam
)
GO

For the parameter to the second function, the IntList type is very simple:

USE [test]
GO

/****** Object:  UserDefinedTableType [dbo].[IntList]    Script Date: 10/9/2018 6:55:56 AM ******/
CREATE TYPE [dbo].[IntList] AS TABLE(
    [Value] [int] NOT NULL
)
GO

How do I execute the first function as part of the query in the second function?

@DenisRubashkin I could have sworn I had tried that ... it seems to fix the problem. Thanks! — Giffyguy, Oct 09 '18 at 14:12
Possible duplicate of [ is not a recognized built-in function name](https://stackoverflow.com/questions/9575622/table-valued-function-is-not-a-recognized-built-in-function-name) — Erik A, Oct 09 '18 at 20:00
Possible duplicate of [is not a recognized built-in function name](https://stackoverflow.com/q/41218952) — Erik A, Oct 09 '18 at 20:00

score 2 · Accepted Answer · answered Oct 09 '18 at 14:26

2

Add the schema name: dbo.NameFromEnumerationID( [Value] )

dba.stackexchange.com: Why is the schema prefix (dbo) mandatory when we call a function?

answered Oct 09 '18 at 14:26

Denis Rubashkin

2,151
1
9
14

SQL Server: Cannot execute existing scalar function from within table-valued function

1 Answers1