Using OPTION clause within CREATE FUNCTION statement for inline table functions

Question

Hi!

 

I need to expand resursion level for resursive CTE expression within CREATE FUNCTION statement for inline table function to a value greater than default. It turns out that OPTION clause for MAXRECURSION hint perfectly works if I use it outside CREATE FUNCTION (as well as CREATE VIEW for non-parametrized queries), but  it does not within CREATE FUNCTION statement - I'm getting error:

Msg 156, Level 15, State 1, Procedure ExpandedCTE, Line 34

Incorrect syntax near the keyword 'option'.

 

Here is the function:

 

create FUNCTION [dbo].[ExpandedCTE]

(

@p_id int

)

RETURNS TABLE

AS

RETURN

(

with tbl_cte (id, tbl_id, lvl)

as

(

select

id, tbl_id, 0 lvl

from

tbl

where

id = @p_id

union all

select

t.id, t.tbl_id, lvl + 1

from

tbl_cte

inner join tbl t

on rnr.tbl_id = tbl_cte.id

)

select

id, tbl_id, lvl

from

tbl_cte

option (maxrecursion 0)

)

 

Please help!  

 

Alexander.

 

P.S.

I'm really sorry if it is about syntax, but I could not find it in the documentation.

Answer 1

I do not immediately see the issue and without the the tables its is difficult to see what is happening.  It appears that the recursion option is properly set, although I have never included this within a funtion.  I am wondering about the anchor cte the third column in the SELECT clause, highlighted in yellow.  This does not appear to be a valid column name and is not enclosed in delimited identifiers.

 

 

Code Snippet

CREATE FUNCTION [dbo].[ExpandedCTE]

(@p_id int)

RETURNS TABLE

AS

RETURN

(

WITH tbl_cte (id,

 tbl_id,

    lvl)

AS

(

SELECT id,

tbl_id,

0 Lvl

FROM tbl

WHERE id = @p_id

 

UNION ALL

 

SELECT t.id,

t.tbl_id,

lvl + 1

FROM tbl_cte INNER JOIN tbl t

ON rnr.tbl_id = tbl_cte.id

)

 

SELECT id,

tbl_id,

lvl

FROM tbl_cte

OPTION(MAXRECURSION 0);

)

 

 

 

Answer 2

Let us know what happens if you place a semi-colon after the maxrecursion option.

 

   option (maxrecursion 0);

 

Answer 3

David,

 

Lvl states for lvl - misspelling, sorry, my bad. I've fixed that in the post.

 

Thank you!

 

Alexander.

 

Answer 4

Hi Arnie,

 

I've done that. The result is equally the same.

 

If pertinent, this is the result I get from xp_msver:

 

Index Name Internal_Value Character_Value

------ -------------------------------- -------------- ------------------------------------------------------------------------------------------------------------------------

1 ProductName NULL Microsoft SQL Server

2 ProductVersion 589824 9.00.3042.00

3 Language 1033 English (United States)

4 Platform NULL NT INTEL X86

5 Comments NULL NT INTEL X86

6 CompanyName NULL Microsoft Corporation

7 FileDescription NULL SQL Server Windows NT

8 FileVersion NULL 2005.090.3042.00

9 InternalName NULL SQLSERVR

10 LegalCopyright NULL © Microsoft Corp. All rights reserved.

11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation

12 OriginalFilename NULL SQLSERVR.EXE

13 PrivateBuild NULL NULL

14 SpecialBuild 199360512 NULL

15 WindowsVersion 248381957 5.2 (3790)

16 ProcessorCount 1 1

17 ProcessorActiveMask 1 00000001

18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM

19 PhysicalMemory 1023 1023 (1073164288)

20 Product ID NULL NULL

 

Thank you!

 

Alexander.

Answer 5

OPTION clause can be used only at the statement level. Ex:

 

Code Snippet

insert into T ... option(keepfixed plan);

update T set .... option(keepfixed plan);

delete from T ... option(keepfixed plan);

select * from T  option(keepfixed plan);

go

create proc p1

as

return  select count(*) from T option(keepfixed plan);

go

declare @c cursor;
set @c = cursor fast_forward for select * from T option(keepfixed plan);

go

declare cr cursor fast_forward for select * from T option(keepfixed plan);

deallocate cr;

go

 

So you cannot use it within a query expression inside view definitions or inline TVFs etc. The only way to use it in your case is to create the TVF without the OPTION clause and specify it in the query that uses the TVF. We have a bug that tracks request for allowing use of OPTION clause inside any query expression (for example, if exists() or CTE or view).

Answer 6

Alexander,

 

You can not change the default value of that option inside a udf. You will have to do it in the statement referencing the udf.

 

select *

from [dbo].[ExpandedCTE](1) as t

option (MAXRECURSION 0)

 

 

AMB

Answer 7

Guys,

 

Thank you all! Just one more question regarding this... I'll try it out of course, but does this approach work in case of long dependencies chain, when the function with CTE definition is referenced in other function that in turn referenced in other function and etc?

 

Thank you!

 

Alexander.

 

 

Answer 8

Alexander,

 

See by yourself.

 

Code Snippet

create function dbo.ufn_numbers (

@i int

)

returns table

as

return (

with cte

as

(

select 1 as number

union all

select number + 1 as number

from cte

where number <= @i

)

select number

from cte

)

go

 

create function dbo.ufn_f1 (

@i int

)

returns table

as

return (

select number

from dbo.ufn_numbers(@i)

)

go

 

select * from dbo.ufn_f1(100)

go

 

-- will fail

select * from dbo.ufn_f1(101)

go

 

select * from dbo.ufn_f1(101) option (maxrecursion 0)

go

 

drop function dbo.ufn_f1

go

 

drop function dbo.ufn_numbers

go

 

 

 

AMB

Answer 9

What if the UDF if being called from a UDF?  I am having this problem now.