Solved

with inline = on - iTVF subroutine causing errors

  • 21 November 2022
  • 8 replies
  • 72 views
J. de Lange
Rank
Userlevel 2
Badge +4

I’m trying to create an inlineable Table Valued Function. I’ve created a subroutine with option INLINE set to ON. The function has a nearly empty template only containing:

return;

whenever I try to execute the source code I get this error:

Msg 487, Level 16, State 1, Procedure get_calculation_component_overview, Line 47 [Batch Start Line 6]
An invalid option was specified for the statement "CREATE/ALTER FUNCTION".

The function executes fine when I remove the line

with inline = on

The database compatibility is 150 and we’re using SQL server 2019:
 


Why isn’t this working?

icon

Best answer by J. de Lange 21 November 2022, 17:27

View original
This topic has been closed for comments

8 replies

Mark Jongeling
Rank
Userlevel 7
Badge +21

Hi,

Could this be the problem?: https://dba.stackexchange.com/questions/264596/subscription-initialization-throws-error-the-option-inline-on-is-not-valid-fo

There may be an issue surrounding this inside SQL2019 at some point. I hope the thread above can help out.

"Wake me up before you low-code"
J. de Lange
Rank
Userlevel 2
Badge +4

  

Hi,

Could this be the problem?: https://dba.stackexchange.com/questions/264596/subscription-initialization-throws-error-the-option-inline-on-is-not-valid-fo

There may be an issue surrounding this inside SQL2019 at some point. I hope the thread above can help out.

This thread mainly seems to address an old issue that caused the with inline = on to be added to incompatible table valued functions and this was supposedly fixed in with the QL 2019 CU6 update. We’re at CU8. 

I want the with inline = on option. I specifically set it using the subroutine options in the SF. The problem is that this always results in an error.

The thread does contain a single comment mentioning a problem with CU9 but googling this problem doesn’t give any useful results. 

Has there been any success creating inline table valued functions? And is this option in the SF the way to go about creating them?

Mark Jongeling
Rank
Userlevel 7
Badge +21

When the Function cannot be inline an error will be thrown as specified here. Does the function cover all requirements of an inline function? 

"Wake me up before you low-code"
J. de Lange
Rank
Userlevel 2
Badge +4

It’s an empty template only containing return;
After executing it on the database without with inline = on and checking the inlineable compatibility with 
 

select o.*
from sys.sql_modules o
where exists(
	select 1
	from sys.objects x
	where x.object_id = o.object_id
	and x.name like '%get_calculation%'
)

I can see that is_inlineable is set to 0. Which would indicate that the function doesn’t meet the requirements. I wonder what requirement and empty function doesn’t meet though. 

Adding a tiny template which inserts a row into the table variable which gets returned doesn’t change it either.

I’ll check if there are any other requirements I do not meet with this function.

J. de Lange
Rank
Userlevel 2
Badge +4

I can’t get this to work. Manually altering the create function query made it possible to create a table valued function that seems to meet the requirements. 
 

if exists (select 1 from sysobjects
           where name = 'get_calculation_component_overview' and (type = 'FN' or type = 'TF' or type = 'IF'))
   drop function "get_calculation_component_overview"
go
create function "get_calculation_component_overview"
(
   @calculation_id   bigint   
)
returns table
--with inline = on
as
 return select * from calculation_component cc
 where cc.calculation_id = @calculation_id

go



is_inlineable is now set to 1:

If I try to set the with inline = on option it still throws the  “invalid option was specified”  error...

Mark Jongeling
Rank
Userlevel 7
Badge +21

I think with inline = on is not available to TVF, only to Scalar UDF, so table-valued functions cannot be inlineable using this statement.

"Wake me up before you low-code"
J. de Lange
Rank
Userlevel 2
Badge +4

I think with inline = on is not available to TVF, only to Scalar UDF, so table-valued functions cannot be inlineable using this statement.

This might be it. It seems the option isn’t available. There are loads of blog posts mentioning that it is possible to inline TVF’s but none of them use this option. I’ll check if it’s possible to inline a TVF without using the option.

J. de Lange
Rank
Userlevel 2
Badge +4

After some testing, TVF’s with the is_inlineable property set to 1 appear to be inlined automatically.

For anyone else who wants to inline a TVF:

TVF’s defined using the SF with the Return table value set will always fail the requirements and won’t be inlined.

Instead leave the return table empty and don’t set the Inline option:

In the template don’t insert values into a table variable, instead return a set immediately: 
 

 

Check if your TVF is inlineable by using:

select o.*
from sys.sql_modules o
where exists(
	select 1
	from sys.objects x
	where x.object_id = o.object_id
	and x.name = <your TVF name>
)

if is_inlined is set to 1 it should be inlined automatically by SQL server (2019 and up)

It works like a dream now.

Terms & ConditionsCookie settings