CODEWITHSUNDEEP
sql-server
Rob Peterson
Rob Peterson

Reputation: 175

Why do I get a 11555 error on this SQL Server inline table valued function?

I am trying to make this function an inline table-valued function on SQL Server 2016.

CREATE FUNCTION ufn_GetBusinessRuleValue
(   
    -- Add the parameters for the function here
    @FieldName VARCHAR(MAX) NOT NULL
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT
        FieldValue
    FROM 
        [BVI].[dbo].[BusinessRules] br
    WHERE 
        br.ClientID = DB_NAME()
        AND DATEDIFF(DAY, GETDATE(), ActiveDate) <= 0
        AND (DATEDIFF(DAY, GETDATE(), InactiveDate) >= 0 OR InactiveDate IS NULL)
        AND br.FieldName = 'ufn_GetBVIEmpType.Configuration'
)

When I try to create it with not null parameters I get the following error message:

Msg 11555, Level 15, State 1, Procedure ufn_GetBusinessRuleValue, Line 9 [Batch Start Line 15]
The parameter '@FieldName' has been declared as NOT NULL. NOT NULL parameters are only supported with natively compiled modules, except for inline table-valued functions.

I thought this was an inline table-valued function. Is this not really an inline table-valued function? What am I doing wrong?

Upvotes: 1

Views: 715

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176224

You need to remove NOT NULL because it is not supported with inline TVP:

CREATE FUNCTION ufn_GetBusinessRuleValue
(   
    -- Add the parameters for the function here
    @FieldName VARCHAR(MAX) 
)

CREATE FUNCTION:

NULL|NOT NULL

Supported only for natively compiled, scalar user-defined functions.

Upvotes: 1

Related Questions