How does one Create a Parameterized Recursive CTE to flatten a heirarchy within a Scalar Function?

Question

I'm trying to create a scalar function to determine whether a user of a provided ID or any of their subordinates have orders under a collection of provided order IDs.

Note I am using my own User-Defined Table Type of IntegerIdTableType to take in the collection of OrderIds.

CREATE FUNCTION DoOrdersExistUnderUserOrUsersSubordinates
(
    @orderIds dbo.IntegerIdTableType READONLY,
    @userId INT
)
RETURNS BIT
AS
BEGIN
    RETURN 
    (
        WITH GetUserIds(ordinateUserId)
        AS
        (
            SELECT ordinateUserId UserId
            UNION ALL
            SELECT GetUserIds(Subordinate.Id)
            FROM UsersAccounts.Users Subordinates
            WHERE Subordinates.SupervisorId = @ordinateUserId
        )
        SELECT CASE WHEN EXISTS
        (
            SELECT 1
            FROM Orders
            WHERE Orders.Id IN
            (
                SELECT Id
                FROM @orderIds
            )
            AND Orders.UserId IN
            (
                SELECT UserId
                FROM GetUserIds(@userId)
            )
        )
        THEN CAST(1 AS BIT)
        ELSE CAST(0 AS BIT)
        END
    )
END

Here is some sample data for both my Orders and Users tables.

Users

Orders

Expected Results

When calling DoOrdersExistUnderUserOrUsersSubordinates with the following values, I expect the following results.

I'm having 2 issues with this function:

1. Syntax errors:

   Incorrect syntax near the keyword 'WITH'.

   Incorrect syntax near ')'.

2. 'GetUserIds' is not a recognized built-in function name

   The above seems to happen even without being wrapped in a function.

I don't know what the correct way to pass a parameter to a recursive CTE is but I have seen examples where the declaration of the CTE has a name in brackets which I assumed to be a parameter

I've tried putting a semi-colon immediately before the WITH even though it's the only statement in the function and I just get Incorrect syntax near ';'. instead of Incorrect syntax near the keyword 'WITH'.

I've also tried getting rid of the BEGIN and END and that gives me Incorrect syntax near 'RETURN'., plus Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. If I don't include the redundant semi-colon.

How do I get around all of this?

Surely Recursive CTE's must be able to take in a parameter or what would they recurse on?

UPDATE:

After battling with Example F of the documentation linked by Zohar_Peled, I eventually figured out that parameters aren't passed into the CTE as such, but rather joined to it then persisted within it through the brackets of its declaration. Whatever is then defined in the corresponding SELECTs is output through the parameters to whatever called the CTE (in this case, either the outer SELECT Id FROM UserNodes statement or the CTE itself (for the recursion)).

I changed the SQL statement within the function to the following and it worked as expected outside of the function.

WITH UserNodes([Root User ID], Id, SupervisorId)
AS
(
    SELECT Users.Id, Users.Id, Users.SupervisorId
    FROM UsersAccounts.Users
    WHERE Users.SupervisorId IS NULL
    UNION ALL
    SELECT [Root User ID],
        Users.Id,
        Users.SupervisorId
    FROM UsersAccounts.Users
    JOIN UserNodes [Subordinate Descendant Users] ON [Subordinate Descendant Users].Id = Users.SupervisorId
)
SELECT CASE WHEN EXISTS
(
    SELECT 1
    FROM Orders
    WHERE Orders.Id IN
    (
        SELECT Id
        FROM @orderIds
    )
    AND Orders.UserId IN
    (
        SELECT Id
        FROM UserNodes
        WHERE [Root User ID] = @userId
    )
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END

This works fine alone (with the required variables provided to substitute the missing function parameters) but as soon as I put it back into the CREATE FUNCTION block, I'm faced with the same syntax errors as before (excluding 2.).

Answer 1

As stated, I'm not able to test this, but this is what I'm suggesting you change:

    CREATE FUNCTION DoOrdersExistUnderUserOrUsersSubordinates
(
    @orderIds dbo.IntegerIdTableType READONLY,
    @userId INT
)
RETURNS BIT
AS
BEGIN

    declare @bln bit

    ;WITH UserNodes([Root User ID], Id, SupervisorId)
    AS
    (
        SELECT Users.Id, Users.Id, Users.SupervisorId
        FROM UsersAccounts.Users
        WHERE Users.SupervisorId IS NULL
        UNION ALL
        SELECT [Root User ID],
            Users.Id,
            Users.SupervisorId
        FROM UsersAccounts.Users
        JOIN UserNodes [Subordinate Descendant Users] ON [Subordinate Descendant Users].Id = Users.SupervisorId
    )
    SELECT @bln = CASE WHEN EXISTS
    (
        SELECT 1
        FROM Orders
        WHERE Orders.Id IN
        (
            SELECT Id
            FROM @orderIds
        )
        AND Orders.UserId IN
        (
            SELECT Id
            FROM UserNodes
            WHERE [Root User ID] = @userId
        )
    )
    THEN CAST(1 AS BIT)
    ELSE CAST(0 AS BIT)
    END

    RETURN @bln
END

Let me know if it works...