equivalent of Postgres WITH RECURSIVE in Microsoft MSSQL?

Question

I have an existing WebApp that uses a Postgres (9.4) db. The application has a fairly standard permissions structure, with Users belonging to Groups, and Levels/Departments within the organization in a hierarchy (a Level has a Parent-Level).

The relevant part of the DataModel is:

create table level(level_id int primary key, parent_level int);
create table grouplevel(level_id int, group_id int);
create table usergroup(user_id int, group_id int);

The app uses Postgres' WITH RECURSIVE feature to traverse the Level tree, to collect a hierarchical list of level_ids that a User has Group permission on. In other words, it lists the top-tier Levels first, then their children, and the children's children in that order

SELECT
level_id
FROM
(
    WITH RECURSIVE tree(level_id, root)AS(
        SELECT
            C .level_id,
            C .parent_level
        FROM
            LEVEL C
        LEFT JOIN LEVEL P ON C .level_id = P .parent_level
        WHERE
            P .level_id IN(
                SELECT
                    GL.level_id
                FROM
                    GROUPLEVEL GL
                JOIN USERGROUP UG ON(GL.GROUP_ID = UG.GROUP_ID)
                WHERE
                    USER_ID = 1
            )
        UNION
            SELECT
                tree.level_id,
                root
            FROM
                tree
            INNER JOIN LEVEL ON tree.root = LEVEL .level_id
    )SELECT
        *
    FROM
        tree
)AS T
UNION
(
    SELECT
        GL.level_id
    FROM
        GROUPLEVEL GL
    JOIN USERGROUP UG ON(GL.GROUP_ID = UG.GROUP_ID)
    WHERE
        USER_ID = 1
)

Now we are implementing the App with a MSSQL back-end - is there any way to achieve the same kind of recursive/hierarchical listing in MSSQL?

Just leave out the `recursive` keyword. Although required by the SQL standard, Microsoft chose to ignore that and forbids the use of it. But apart from that, the query should work just fine — , Aug 24 '18 at 07:46
hmm, without `recursive` I get: Incorrect syntax near the keyword 'WITH'. Msg 319, Level 15, State 1, Line 5 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. — Black, Aug 24 '18 at 09:29
Maybe you need to move the CTE to the top level. I am not sure if SQL Server supports "embedded" CTEs — , Aug 24 '18 at 09:31
Those microsoft people always put a `;` *before* the `WITH`. BTW: in `WHERE USER_ID = 1` the `1` is actually a parameter? — joop, Aug 24 '18 at 09:32
sorry I don't follow how to move the common table expression "to the top level" - could you post more detail - in the form of an answer (which I can 'accept')? — Black, Aug 24 '18 at 09:33
check given link for [Recursive Query](https://stackoverflow.com/questions/14518090/recursive-query-in-sql-server) — Hiren Patel, Aug 24 '18 at 09:35
@joop yes it's a parameter (I inserted a value from my MyBatis template) — Black, Aug 24 '18 at 09:36
if I put the semicolon in, I can run the inner query by itself, but not include it as shown as an inner query (error is "Incorrect syntax near ';'.") Is there some trick to this? — Black, Aug 24 '18 at 10:21
BTW: the first part of your outer-query union has two columns, the second leg has only one: `)AS T UNION (...)` Typo? — joop, Aug 24 '18 at 10:35

score 0 · Accepted Answer · answered Aug 24 '18 at 10:19

the inner query can be refactored as:

 ;WITH   tree(level_id, root)AS(
        SELECT
            C .level_id, C .parent_level
        FROM
            LEVEL C
        LEFT JOIN LEVEL P ON C .level_id = P .parent_level
        WHERE
            P .level_id IN(
                SELECT
                    GL.level_id
                FROM
                    GROUPLEVEL GL
                JOIN USERGROUP UG ON(GL.GROUP_ID = UG.GROUP_ID)
                WHERE
                    USER_ID = 1
            )
        UNION all SELECT tree.level_id, root FROM tree
            INNER JOIN LEVEL ON tree.root = LEVEL .level_id
    )SELECT * FROM tree

However I have not yet found a way to contain this within an inner query

equivalent of Postgres WITH RECURSIVE in Microsoft MSSQL?

1 Answers1