Post reply

TVF in recursive part of CTE

  • February 29, 2012 at 11:00 pm

    #257631

    Hi,

    I have checked that a TVF doesn't work in recursive part of cte.

    Is this a limitation or syntax is not followed.

    Can any one provide an example or link on this.

    ;WITH CTE AS(

    SELECT 1 AS AID , 0 AS Depth

    UNION ALL

    SELECT H.ID, Depth+1

    FROM CTE C

    INNER JOIN dbo.tvfChess(C.AID) AS H --------- Table valued function

    ON C.AID = H.ID

    )

    SELECT * FROM CTE

    We get error

    The multi-part identifier "C.AID" could not be bound.

  • March 1, 2012 at 6:43 am

    #1453583

    You should use CROSS APPLY because you can't specify other table column as a table function argument you are joining with:

    ;WITH CTE AS

    (

    SELECT 1 AS AID , 0 AS Depth

    UNION ALL

    SELECT H.ID, Depth+1

    FROM CTE C

    CROSS APPLY dbo.tvfChess(C.AID) AS H --------- Table valued function

    )

    SELECT * FROM CTE

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

  • March 1, 2012 at 6:45 am

    #1453584

    Thanks it worked

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply