So, I used CME instead of CTE (Common Table Expressions) b/c I'm involved in a project in the medical training industry right now and CMEs is top of mind as (Continuing Medical Education). And also b/c the phrase Common Table Expressions isn't very descriptive, at least in terms of using it to solve recursive query problems.

I did some research and some digging to get to the bottom of how I could effectively use CTEs in a stored procedure to return the records I needed and it wasn't too hard after all. I've documented the CREATE statements you'll need below to do the following:

1.) Return the given account record and all children and all their children and so on.

2.) Given an account record, return only the children and all their children and so on. This procedure does not return the parent account passed. It can return no records if the account has no children.

3.) Given any account, this procedure returns the top-most parent in that account tree. This is widely used throughout my application since several settings for users of accounts are stored only at the top-most level of the organization.

The procedures:

1.) CREATE PROCEDURE GET_ACC_TREE @ACC_ID INT AS

WITH ACC_TREE (ACC_ID, ACC_TITLE, ACC_ACC_ID, ACC_LEVEL, ACC_SORT) AS (SELECT A.ACC_ID, A.ACC_TITLE, A.ACC_ACC_ID, 0, CAST (A.ACC_ID AS VARBINARY(900)) FROM ACC A WHERE A.ACC_ID = @ACC_ID UNION ALL SELECT A.ACC_ID, A.ACC_TITLE, A.ACC_ACC_ID, (B.ACC_LEVEL + 1), CAST (B.ACC_SORT + CAST (A.ACC_ID AS BINARY(4)) AS VARBINARY(900)) FROM ACC A INNER JOIN ACC_TREE B ON A.ACC_ACC_ID = B.ACC_ID) SELECT ACC_ID, ACC_TITLE FROM ACC_TREE ORDER BY ACC_SORT ASC, ACC_LEVEL ASC OPTION (MAXRECURSION 4)

2.) CREATE PROCEDURE GET_ACC_CHILDREN @ACC_ID INT AS

WITH ACC_CHILDREN (ACC_ID, ACC_TITLE, ACC_ACC_ID, ACC_LEVEL, ACC_SORT) AS (SELECT A.ACC_ID, A.ACC_TITLE, A.ACC_ACC_ID, 0, CAST (A.ACC_ID AS VARBINARY(900)) FROM ACC A WHERE A.ACC_ACC_ID = @ACC_ID UNION ALL SELECT A.ACC_ID, A.ACC_TITLE, A.ACC_ACC_ID, (B.ACC_LEVEL + 1), CAST (B.ACC_SORT + CAST (A.ACC_ID AS BINARY(4)) AS VARBINARY(900)) FROM ACC A INNER JOIN ACC_CHILDREN B ON A.ACC_ACC_ID = B.ACC_ID) SELECT ACC_ID, ACC_TITLE FROM ACC_CHILDREN ORDER BY ACC_SORT ASC, ACC_LEVEL ASC OPTION (MAXRECURSION 4)

3.) CREATE PROCEDURE GET_ACC_TOP @ACC_ID INT AS

WITH ACC_TREE (ACC_ID, ACC_TITLE, ACC_ACC_ID, ACC_LEVEL, ACC_SORT) AS (SELECT A.ACC_ID, A.ACC_TITLE, A.ACC_ACC_ID, 0, CAST (A.ACC_ID AS VARBINARY(900)) FROM ACC A WHERE A.ACC_ID = @ACC_ID UNION ALL SELECT A.ACC_ID, A.ACC_TITLE, A.ACC_ACC_ID, (B.ACC_LEVEL + 1), CAST (B.ACC_SORT + CAST (A.ACC_ID AS BINARY(4)) AS VARBINARY(900)) FROM ACC A INNER JOIN ACC_TREE B ON A.ACC_ID = B.ACC_ACC_ID) SELECT ACC_ID, ACC_TITLE FROM ACC_TREE WHERE ACC_ACC_ID IS NULL ORDER BY ACC_SORT ASC, ACC_LEVEL ASC OPTION (MAXRECURSION 4)

When calling one of these procedures using inline SQL, simply execute the following query:

EXEC DBO.GET_ACC_TOP @ACC_ID = 5

As an example:

rs.open "EXEC DBO.GET_ACC_TOP @ACC_ID = 5", conn

Happy programming!