none
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.

    Tuesday, May 13, 2008 8:20 PM

Answers

  • 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).

    Tuesday, May 13, 2008 10:09 PM
  • 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

    Tuesday, May 13, 2008 10:25 PM
    Moderator
  • 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

    Wednesday, May 14, 2008 1:57 PM
    Moderator

All replies

  • 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);

    )

     

     

     

    Tuesday, May 13, 2008 9:20 PM
    Moderator
  • Let us know what happens if you place a semi-colon after the maxrecursion option.

     

       option (maxrecursion 0);

     

    Tuesday, May 13, 2008 9:35 PM
    Moderator
  • David,

     

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

     

    Thank you!

     

    Alexander.

     

    Tuesday, May 13, 2008 9:50 PM
  • 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.

    Tuesday, May 13, 2008 9:59 PM
  • 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).

    Tuesday, May 13, 2008 10:09 PM
  • 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

    Tuesday, May 13, 2008 10:25 PM
    Moderator
  • Guys,

     

    Thank you all! Smile 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.

     

     

    Wednesday, May 14, 2008 8:00 AM
  • 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

    Wednesday, May 14, 2008 1:57 PM
    Moderator
  • What if the UDF if being called from a UDF?  I am having this problem now.
    Wednesday, October 08, 2008 5:22 PM