如何创建参数化递归CTE来平化标量函数中的层次结构? [英] How does one Create a Parameterized Recursive CTE to flatten a heirarchy within a Scalar Function?

问题描述

我正在尝试创建一个标量函数，以确定提供的ID的用户或其任何下属是否在提供的订单ID的集合下拥有订单.

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.

请注意，我正在使用自己的用户定义的表类型IntegerIdTableType来接收OrderId的集合.

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.

用户

订单

预期结果

使用以下值调用DoOrdersExistUnderUserOrUsersSubordinates时，我期望得到以下结果.

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

此功能有2个问题:

1. 语法错误:

1. Syntax errors:

关键字"WITH"附近的语法不正确.

Incorrect syntax near the keyword 'WITH'.

')'附近的语法不正确.

Incorrect syntax near ')'.

'GetUserIds'不是公认的内置函数名称

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

即使没有将其包装在函数中，上述情况似乎也会发生.

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

我不知道将参数传递给递归CTE的正确方法是什么，但是我看到了一些示例，其中CTE的声明在方括号中有一个我认为是参数的名称

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

即使它是函数中唯一的语句，我也尝试在WITH之前放置一个分号，并且我只是在';'附近得到不正确的语法.而不是关键字"WITH"附近的语法不正确.

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

我还尝试摆脱BEGIN和END，这使我在附近使用不正确的语法.，以及在关键字'with'附近使用不正确的语法.如果此语句是公用表表达式，xmlnamespaces子句或变更跟踪上下文子句，则前一条语句必须以分号终止.如果我不包括冗余分号.

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?

递归CTE必须能够接受参数，否则它们将基于什么递归?

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

更新:

在与Zohar_Peled链接的文档的示例F 进行斗争之后，我最终发现，参数并没有像这样传递到CTE中，而是加入了它，然后通过括号保留在其中它的声明.然后，将在相应SELECT中定义的内容通过参数输出到称为CTE的任何内容(在这种情况下，是外部SELECT Id FROM UserNodes语句或CTE本身(用于递归)).

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

我将函数内的SQL语句更改为以下内容，并在函数外按预期工作.

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

这单独可以很好地工作(提供必需的变量来替代缺少的函数参数)，但是一旦我将其放回CREATE FUNCTION块中，我就面临着与以前相同的语法错误(不包括2). ).

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

推荐答案

如上所述，我无法对此进行测试，但这是我建议您进行的更改:

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

这篇关于如何创建参数化递归CTE来平化标量函数中的层次结构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！