Solved

Need UDF to return table variable that traverses an organization hierarchy

Posted on 2013-06-29
10
547 Views
Last Modified: 2013-07-01
Fellow Experts,

Having never quite gotten the hang of recursive queries, I could use your help here.  (If this can be done without a recursive query, great.)  This will be run in SQL Server 2008 R2.

Consider the following org chart:

Example org chart
In that phony org chart, each box represents a "team", and each team has zero or more "users" assigned to it.  A user is only ever assigned to one team.

The teams are stored in the database in a User_Teams table (TeamID, TeamName, ParentTeamID).  The users are stored in the database in a User_List table (UserID, UserDispName, TeamID).

What I need is a UDF that, given a particular TeamID, returns a table of all the UserID values for the users assigned to that team, and also the UserID values for all of the teams that fall under that team in the hierarchy.

Thus, in the example above, if I supplied 96 for the @TeamID parameter (that is, the TeamID for Sitcom Central), I would expect the UDF to return a table of all of UserID values for all teams shaded in yellow.  Based on the sample data provided here, I would expect the UDF to return a table with the following values:

268   (Parr Jack )
269   (THE Witch Endora )
270   (Stevens Darren )
271   (Stevens Samantha )
272   (Stevens Tabatha )
273   (Brady Mike )
274   (Mertz Fred )
275   (Maid Alice )
276   (Ricardo Ricky )
277   (Brady Cindy )
278   (Brady Bobby )

(Note that I only need the integer IDs.  I added the names for ease of reference only.)

You can use the following to generate test data:

CREATE TABLE [dbo].[User_Teams](
	[TeamID] [int] NOT NULL,
	[TeamName] [nvarchar](50) NOT NULL,
	[ParentTeamID] [int] NULL,
)

GO

INSERT INTO User_Teams (TeamID, TeamName, ParentTeamID) VALUES
(3, 'My Corporation', 0),
(4, 'Team 1', 3),
(5, 'Team 2', 4),
(6, 'Team 3', 4),
(7, 'Team 4', 4),
(8, 'Team 5', 17),
(9, 'Team 6', 8),
(10, 'Team 7', 17),
(11, 'Team 8', 10),
(12, 'Team 9', 11),
(13, 'Team 10', 3),
(14, 'Team 11', 5),
(15, 'Team 12', 5),
(16, 'Team 13', 4),
(17, 'Team 14', 5),
(18, 'Team 15', 17),
(19, 'Team 16', 3),
(20, 'Team 17', 10),
(21, 'Team 18', 3),
(22, 'Team 19', 4),
(23, 'Team 20', 22),
(24, 'Team 21', 23),
(25, 'Team 22', 23),
(26, 'Team 23', 23),
(27, 'Team 24', 23),
(28, 'Team 25', 23),
(29, 'Team 26', 13),
(30, 'Team 27', 13),
(31, 'Team 28', 13),
(32, 'Team 29', 4),
(33, 'Team 30', 32),
(34, 'Team 31', 32),
(35, 'Team 32', 32),
(36, 'Team 33', 4),
(37, 'Team 34', 36),
(38, 'Team 35', 36),
(39, 'Team 36', 13),
(40, 'Team 37', 3),
(41, 'Team 38', 35),
(42, 'Team 39', 23),
(43, 'Team 40', 4),
(44, 'Team 41', 4),
(45, 'Team 42', 4),
(46, 'Team 43', 45),
(47, 'Team 44', 4),
(48, 'Team 45', 47),
(49, 'Team 46', 3),
(50, 'Team 47', 49),
(51, 'Team 48', 50),
(52, 'Team 49', 51),
(53, 'Team 50', 22),
(54, 'Team 51', 4),
(55, 'Team 52', 22),
(56, 'Team 53', 55),
(57, 'Team 54', 55),
(58, 'Team 55', 55),
(59, 'Team 56', 16),
(60, 'Team 57', 23),
(61, 'Team 58', 13),
(62, 'Team 59', 47),
(63, 'Team 60', 55),
(64, 'Team 61', 22),
(65, 'Team 62', 3),
(66, 'Team 63', 23),
(67, 'Team 64', 23),
(68, 'Team 65', 23),
(69, 'Team 66', 4),
(70, 'Team 67', 17),
(71, 'Team 68', 17),
(72, 'Team 69', 9),
(73, 'Team 70', 22),
(74, 'Team 71', 36),
(75, 'Team 72', 36),
(76, 'Team 73', 3),
(77, 'Team 74', 3),
(78, 'Team 75', 77),
(79, 'Team 76', 3),
(80, 'Team 77', 77),
(81, 'Team 78', 78),
(82, 'Team 79', 77),
(83, 'Team 80', 81),
(84, 'Team 81', 71),
(85, 'Team 82', 71),
(86, 'Team 83', 71),
(90, 'Team 84', 79),
(91, 'Team 85', 90),
(92, 'Team 86', 91),
(93, 'Team 87', 92),
(95, 'Team 88', 45),
(96, 'Sitcom Central', 3),
(97, 'Brady Bunch', 96),
(98, 'Family Room', 97),
(99, 'Team Boys', 98),
(100, 'Marsha, Marsha, Marsha', 96),
(101, 'Mikes Den', 97),
(102, 'Team Parents', 101),
(103, 'Team Housekeeper', 101),
(104, 'I Love Lucy', 96),
(105, 'The Tropicana Lounge', 104),
(106, 'Team 99', 35),
(107, 'Team 100', 3),
(108, 'Team 101', 98),
(109, 'Team 102', 4),
(110, 'Team 103', 109),
(111, 'Team 104', 110),
(112, 'Team 105', 110),
(113, 'Team 106', 107),
(114, 'Team 107', 107),
(116, 'The Ricardo Residence', 104),
(117, 'Bewitched', 96),
(118, 'Team 110', 107),
(120, 'Team 111', 50),
(121, 'Team 112', 120),
(123, 'Team 113', 3),
(124, 'Team 114', 3),
(125, 'Team 115', 124),
(126, 'Team 116', 79),
(127, 'Team 117', 126),
(128, 'Team 118', 127),
(129, 'Team 119', 128),
(130, 'Team 120', 126)

GO

CREATE TABLE [dbo].[User_List](
	[UserID] [int] NOT NULL,
	[UserDispName] [nvarchar](256) NOT NULL,
	[TeamID] [int] NULL,
)

GO

INSERT INTO User_List (UserID, UserDispName, TeamID) VALUES
(1, 'Goforth Julia', 5),
(2, 'Slack Sophia', 4),
(3, 'Smallwood Chanel', 118),
(4, 'Stevens Faye', 118),
(5, 'Guest Elouise', 118),
(6, 'Beltran Maricela', 118),
(7, 'Mcalister Julius', 118),
(8, 'Nixon Carey', 118),
(9, 'Causey Jendayi', 118),
(10, 'Hopson Jinelle', 118),
(11, 'Luttrell Eris', 118),
(12, 'Reynolds Jovanni', 118),
(13, 'Call Ilithya', 118),
(14, 'Winchester Nituna', 118),
(15, 'Reynoso Elian', 118),
(16, 'Valdes Denzel', 118),
(17, 'Lovejoy Jonathon', 118),
(18, 'Ennis Amelia', 118),
(19, 'Jones Jaimie', 118),
(20, 'Cockrell Glenn', 118),
(21, 'Taggart Graciela', 118),
(22, 'Clawson Mira', 118),
(23, 'Edmonds Jared', 118),
(24, 'Maguire Annette', 118),
(25, 'Mello Kari', 118),
(26, 'Mccrary Barrett', 118),
(27, 'Hidalgo Doreen', 118),
(28, 'Calvert Jean', 118),
(29, 'Brooks Andralyn', 118),
(30, 'Pryor Isoke', 118),
(31, 'Harness Whitney', 118),
(32, 'Drummond Hisana', 118),
(33, 'Speed Joletta', 118),
(34, 'Hornsby Isis', 118),
(35, 'Raney Wyman', 118),
(36, 'Borrego Latoya', 118),
(37, 'Trotter Jaime', 118),
(38, 'Seitz Cana', 118),
(39, 'Christman Idana', 118),
(40, 'Hairston Micheal', 118),
(41, 'Milton Mya', 118),
(42, 'Coe Jeremiah', 118),
(43, 'Seymore Roland', 118),
(44, 'Jack Ghada', 118),
(45, 'Macon Irma', 118),
(46, 'Carmona Alexander', 118),
(47, 'Tobin Wade', 118),
(48, 'Larry Harmon', 118),
(49, 'Peachey Lewa', 118),
(50, 'Baker Mose', 118),
(51, 'Larose Polly', 118),
(52, 'Briggs Keola', 118),
(53, 'Wagoner Yazmin', 118),
(54, 'Leslie Kennedi', 118),
(55, 'Oconner Gaura', 118),
(56, 'Villalobos Essie', 118),
(57, 'Proctor Terri', 118),
(58, 'Linville Irvin', 118),
(59, 'Pickering Rogers', 118),
(60, 'Spears Raekwon', 118),
(61, 'Bowlin Marlene', 118),
(62, 'Gooding Earline', 118),
(63, 'Sells Sarina', 118),
(64, 'Callaghan Harmon', 118),
(65, 'Ripley Elna', 118),
(66, 'Batts Ricardo', 118),
(67, 'Smoot Jamir', 118),
(68, 'Woo Alexandra', 118),
(69, 'Patterson Kieu', 118),
(70, 'Bowden Wilbert', 118),
(71, 'Horan Jessie', 118),
(72, 'Ferris Bian', 118),
(73, 'Putnam Adeola', 118),
(74, 'Dutton Trudy', 118),
(75, 'Frasier Elpida', 118),
(76, 'Pelletier Mikaela', 118),
(77, 'Matlock Chandra', 118),
(78, 'Rountree Enoch', 118),
(79, 'Thibodeau Khalil', 118),
(80, 'Seal Sebastian', 118),
(81, 'Wingfield Berenice', 118),
(82, 'Patterson Noe', 118),
(83, 'Blais Karmita', 118),
(84, 'Phipps Verona', 118),
(85, 'Conn Dorene', 118),
(86, 'Lovelace Jailyn', 118),
(87, 'George Elwyn', 118),
(88, 'Rooney Magdalene', 118),
(89, 'Packer Dayana', 118),
(90, 'Crews Clay', 118),
(91, 'Blanchard Jefferson', 114),
(92, 'Morrissey Donal', 114),
(93, 'Burroughs Giana', 114),
(94, 'Boisvert Hannah', 114),
(95, 'Childress Chad', 114),
(96, 'Kish Kaden', 114),
(97, 'Krause Tory', 114),
(98, 'Jeffrey Agnes', 114),
(99, 'Saenz Abigail', 114),
(100, 'Marcum Letitia', 114),
(101, 'Montano Conley', 114),
(102, 'Clem Juwan', 114),
(103, 'Little Joel', 114),
(104, 'Weatherly Stephanie', 114),
(105, 'Teague Ceana', 114),
(106, 'Jorgenson Martina', 114),
(107, 'Peck Vivian', 114),
(108, 'Lloyd Natesa', 114),
(109, 'Bradshaw Cannon', 114),
(110, 'Cintron Bina', 114),
(111, 'Tanner Accalia', 114),
(112, 'Gorham Ervin', 114),
(113, 'Riddick Storm', 114),
(114, 'Beckwith Anais', 114),
(115, 'Shade Tavon', 114),
(116, 'Barrera Lelia', 114),
(117, 'Lindstrom Rachel', 114),
(118, 'Lankford Perry', 114),
(119, 'Hines Augustine', 114),
(120, 'Messina Pat', 114),
(121, 'Tellez Kailee', 114),
(122, 'Harley Deonte', 114),
(123, 'Cecil Jahzara', 114),
(124, 'Leslie Vesta', 114),
(125, 'Brooks Katrina', 114),
(126, 'Covey Darryl', 114),
(127, 'Mccorkle Lafayette', 114),
(128, 'Spencer Isis', 114),
(129, 'Hyde Hayden', 114),
(130, 'Jacques Niko', 114),
(131, 'Dodge Morris', 114),
(132, 'Tarver Maggie', 114),
(133, 'Michaud Rhett', 114),
(134, 'Geiger Dwayne', 114),
(135, 'Montanez Deanna', 114),
(136, 'Stephens Nakia', 114),
(137, 'Koonce Findlay', 114),
(138, 'Martz Morse', 114),
(139, 'Montoya Aryan', 114),
(140, 'Wylie Leoma', 114),
(141, 'Ashby Tayler', 114),
(142, 'Mccauley Oralia', 114),
(143, 'Barr Theo', 114),
(144, 'Odum Gilead', 114),
(145, 'Kemper Ayden', 114),
(146, 'Sylvester Gino', 114),
(147, 'Hassell Cliff', 114),
(148, 'Bull Pearl', 114),
(149, 'Teal Christen', 114),
(150, 'Parry Armani', 114),
(151, 'Bivins Jadyn', 114),
(152, 'Agee Gaetana', 114),
(153, 'Rau Riley', 114),
(154, 'Stein Jordan', 114),
(155, 'Mcknight Anton', 114),
(156, 'Longoria Alton', 114),
(157, 'Wentz Nathen', 114),
(158, 'Zamora Carolina', 114),
(159, 'Duggan Afia', 114),
(160, 'Rich Patrick', 114),
(161, 'Bader Nina', 114),
(162, 'Sell Carys', 114),
(163, 'Stafford Bo', 114),
(164, 'Wise Elmira', 114),
(165, 'Marx Angeline', 114),
(166, 'Stjohn Kylie', 114),
(167, 'Swan James', 114),
(168, 'Mcallister Guadalupe', 114),
(169, 'Wentz Karna', 114),
(170, 'Grey Abena', 114),
(171, 'Robert Viola', 114),
(172, 'Blaylock Jason', 114),
(173, 'Durham Adrian', 114),
(174, 'Freitas Pauline', 114),
(175, 'Lankford Chelsi', 114),
(176, 'Fitzgerald Mathias', 114),
(177, 'Lewandowski Lunette', 114),
(178, 'Willard Michi', 114),
(179, 'Jewell Lynna', 114),
(180, 'Williford Levi', 114),
(181, 'Chesser Roselyn', 114),
(182, 'Harry Kimoni', 114),
(183, 'Jackson Debra', 114),
(184, 'Hubert Yessenia', 114),
(185, 'Newsom Constantine', 114),
(186, 'Hiatt Sofia', 114),
(187, 'Bone Kali', 114),
(188, 'Pitt Linnet', 114),
(189, 'Malley Kinipela', 114),
(190, 'Prieto Kory', 114),
(191, 'Francois Aviva', 114),
(192, 'Putman Elkan', 114),
(193, 'Munoz Beverly', 114),
(194, 'Fredericks Aimee', 114),
(195, 'Fernandes Emera', 114),
(196, 'Speer Lawerence', 114),
(197, 'Harris Evalyn', 114),
(198, 'Bayne Tristian', 114),
(199, 'Hoy Adicia', 114),
(200, 'Mohr Harlen', 114),
(201, 'Oconner Palma', 114),
(202, 'Melendez Dick', 114),
(203, 'Richardson Roxanne', 114),
(204, 'Sorensen Ivan', 114),
(205, 'Pittman Tammy', 114),
(206, 'Moses Kara', 114),
(207, 'Hussey Kaoru', 114),
(208, 'Rickman Aki', 114),
(209, 'Franz Mark', 114),
(210, 'Bollinger Amaris', 114),
(211, 'Olive Lieu', 114),
(212, 'Monaco Dorsey', 114),
(213, 'Drummond Glendon', 114),
(214, 'Stubbs Odell', 114),
(215, 'Poling Cira', 114),
(216, 'Keefer Ellenis', 114),
(217, 'Barnhill Leighanna', 114),
(218, 'Seymour Elnora', 114),
(219, 'Downing Conner', 114),
(220, 'Boucher Aldan', 114),
(221, 'Solano Johari', 114),
(222, 'Perales Winford', 114),
(223, 'Vail Louann', 114),
(224, 'Fredrickson Kaliska', 114),
(225, 'Pennington Geshi', 114),
(226, 'Ramirez Cora', 114),
(227, 'Cowles Lassie', 114),
(228, 'Madden Reagan', 114),
(229, 'Wylie Darshan', 114),
(230, 'Chastain Lessie', 114),
(231, 'Jacobs Keith', 114),
(232, 'Lockhart Janette', 114),
(233, 'Benson Charan', 114),
(234, 'Buford Joshua', 114),
(235, 'East Belle', 114),
(236, 'Gaddy Marlee', 114),
(237, 'Flint Conway', 114),
(238, 'Slattery Grietje', 114),
(239, 'Amaral Nitza', 114),
(240, 'Mccracken Sal', 114),
(241, 'Dorman Darah', 114),
(242, 'Aquino Caden', 114),
(243, 'Parr Glendon', 114),
(244, 'Canady Rodrick', 114),
(245, 'Bisson Elke', 114),
(246, 'Hope Brandie', 114),
(247, 'Bagwell Belita', 114),
(248, 'Moran Alfred', 114),
(249, 'Feliciano Chava', 114),
(250, 'Squires Leigh', 114),
(251, 'Richard Evette', 114),
(252, 'Osborn Darah', 114),
(253, 'Pendleton Arvil', 114),
(254, 'Cothran Leroy', 114),
(255, 'Denson Kaethe', 114),
(256, 'Baer Colette', 114),
(257, 'Means Lecea', 114),
(258, 'Stroup Malachi', 114),
(259, 'Dobson Florine', 114),
(260, 'Burden Ava', 114),
(261, 'Dugan Easter', 114),
(262, 'Kelsey Cascata', 114),
(263, 'Boyer Aisling', 114),
(264, 'Skelton Derrick', 114),
(265, 'Lessard Lilike', 114),
(266, 'Mosby Lindhurst', 21),
(267, 'Walsh Tyron', 39),
(268, 'Parr Jack ', 96),
(269, 'THE Witch Endora ', 117),
(270, 'Stevens Darren ', 117),
(271, 'Stevens Samantha ', 117),
(272, 'Stevens Tabatha ', 117),
(273, 'Brady Mike ', 97),
(274, 'Mertz Fred ', 104),
(275, 'Maid Alice ', 97),
(276, 'Ricardo Ricky ', 105),
(277, 'Brady Cindy ', 108),
(278, 'Brady Bobby ', 99)

Open in new window


Thanks!
0
Comment
Question by:Patrick Matthews
  • 4
  • 3
  • 3
10 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Patrick,

Are you good with using a recursive common table expression or do you want the UDF to recurse? I just want to make sure I understand any restrictions you have.

Kevin

P.S. I will put together a sample with CTE while I wait to hear back.
0
 
LVL 92

Author Comment

by:Patrick Matthews
Comment Utility
Kevin,

Naive question: can you run a CTE within a UDF? :)

The end game is to use the result to help filter another query I am running:

DECLARE @StartDate int, @EndDate int

SET @StartDate = CONVERT(int, CONVERT(varchar, DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0), 112))
SET @EndDate = CONVERT(int, CONVERT(varchar, DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0), 112))

SELECT t.TeamName, u.UserName, u.UserDispName, 
    CASE u.State
        WHEN 0 THEN 'Active'
        WHEN 1 THEN 'Inactive'
        ELSE 'Deleted' END AS UserState,
    al.AppDispName, al.Productivity AS AppProductivity,
    ag.AppGroupName, ag.Productivity AS AppGroupProductivity,
    DATEADD(minute, (p.FrameTime / 100) % 100, 
        DATEADD(hour, p.FrameTime / 10000, 
        CONVERT(datetime, CONVERT(varchar, p.FrameDate)))) AS IntervalDateTime,
    SUM(p.Duration) AS Seconds
FROM Pc_Log p INNER JOIN
    User_List u ON p.PcUser = u.UserID INNER JOIN
    User_Teams t ON u.TeamID = t.TeamID INNER JOIN
    App_List al ON p.Application = al.AppID INNER JOIN
    App_groups ag ON al.AppGroup = ag.AppGroupID
WHERE p.Duration > 0 AND
    p.FrameDate >= @StartDate AND
    p.FrameDate <= @EndDate
GROUP BY t.TeamName, u.UserName, u.UserDispName, u.State, 
    al.AppDispName, al.Productivity, ag.AppGroupName, 
    ag.Productivity, p.FrameDate, p.FrameTime
ORDER BY u.UserName, p.FrameDate, p.FrameTime, al.AppDispName

Open in new window


My original thought was to:
Use a UDF to return a table
Join that table to the query above to only return data for a subset of my users

I'm willing to use any approach that achieves that goal without requiring dynamic SQL.

Going out for a bike ride now before I run out of daylight, so take your time :)

Patrick
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Patrick, you may be able to do this without a UDF completely, depending on a full understanding of the end game, but let's start with your thought. Yes, you can use a CTE in a UDF. I was thinking an in-line UDF for performance reasons.

P.S. I almost have the example. I ran first query thinking users were all subordinates and got:
UserID
-----------
268
269
270
271
272
273
274
275
276

Open in new window


I suspect that one of the levels does not have users, so it missed recursing that leg. I will re-write to recurse teams first then do a single join to user_list.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
Okay, here is the example code:
DECLARE @TeamID INT = 96; /* Sitcom Central */
;WITH teamsCTE(TeamID)
AS 
(
    -- top level
    SELECT TeamID
    FROM User_Teams
    WHERE TeamID = @TeamID

    UNION ALL 

    -- subordinates
    SELECT c.TeamID
    FROM teamsCTE p
    JOIN User_Teams c ON c.ParentTeamID = p.TeamID
)
SELECT UserID
FROM User_List u
WHERE EXISTS (
    /* using EXISTS in case there are ever duplicates.
       a simple JOIN should work just fine, though. */
    SELECT 1
    FROM teamsCTE t
    WHERE t.TeamID = u.TeamID
)
;

Open in new window


Results:
UserID
-----------
268
269
270
271
272
273
274
275
276
277
278

Open in new window


If you know the teams ahead of time, you can use the WHERE EXISTS or JOIN teamsCTE approach in the final selection. You can even make the second query in my example a CTE/derived table itself, so you can filter on UserID.

Function:
CREATE FUNCTION dbo.GetRecursiveUsersByTeam(@TeamID INT)
RETURNS TABLE
AS RETURN
(
WITH teamsCTE(TeamID)
AS 
(
    -- top level
    SELECT TeamID
    FROM User_Teams
    WHERE TeamID = @TeamID

    UNION ALL 

    -- subordinates
    SELECT c.TeamID
    FROM teamsCTE p
    JOIN User_Teams c ON c.ParentTeamID = p.TeamID
)
SELECT UserID
FROM User_List u
WHERE EXISTS (
    /* using EXISTS in case there are ever duplicates.
       a simple JOIN should work just fine, though. */
    SELECT 1
    FROM teamsCTE t
    WHERE t.TeamID = u.TeamID
)
)
GO

Open in new window


I hope that helps!

Kevin

P.S. I hope your had a good bike ride. I am going to run get dinner for the family, so I will be back later if you need anything further.

EDIT: P.P.S. my original concern was on duplicates, so maybe the issue is not JOIN versus EXISTS but GROUP BY on the end results. You can see which works best in the real data.
...
SELECT UserID
FROM User_List u
JOIN teamsCTE t ON t.TeamID = u.TeamID
GROUP BY UserID
...

Open in new window

0
 
LVL 92

Author Comment

by:Patrick Matthews
Comment Utility
Thanks Kevin!  Had a super ride, and now I am going to eat some dinner before trying out your code.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>Having never quite gotten the hang of recursive queries...
recursion can be a bit mind-twisting

Maybe this will help to get your head around it. Instead of worrying about any hierarchy, get the basic recursion construct in your head by generating some numbers. Like this:
-- generate some numbers

;with 
GenNum  --<< the name of this CTE (Common Table Expression)
  (num)
    as (
        select 1       -- A << the "initialize" or "root" query, i.e. we start here
       
        union all      -- B << important, we append more results using this
        
        select num + 1
        from GenNum    -- C << now re-use ITSELF (by its name "GenNum")
        
        where num < 10 -- D << a "LOOP LIMITING COUNTER" i.e. we stop here
                       -- E << but also, repeat from the union all (B)
                       --                until there is no more to do
       )  
select num 
from  GenNum --<< now we can use those results as if it were a table
;

Open in new window

This exact construct is used above in the TeamsCTE provided by mwvisa1, but instead of generating numbers it selects "the next part of hierarchy" and repeats until there is no more to do.
-- your teams
;WITH teamsCTE(TeamID)
AS 
(
    -- top level
    SELECT TeamID
    FROM User_Teams
    WHERE TeamID = @TeamID -- A << the "initialize" or "root" query, i.e. we start here

    UNION ALL              -- B << important, we append more results using this

    -- subordinates
    SELECT c.TeamID
    FROM teamsCTE p         -- C << now re-use ITSELF (by its name "teamsCTE")
    JOIN User_Teams c 
      ON c.ParentTeamID = p.TeamID -- << repeat from the union all (B)
                                   --    until there is no more to do
)

Open in new window

The following video is for DB2 but please don't let that put you off, the DB2 syntax for this is almost identical to SQL Server and I think this chap does a pretty decent job of stepping through the topic. There are a few points raised that are DB2 specific but I think you will spot them easily enough and hence they can be ignored when using SQL Server:
http://www.youtube.com/watch?v=AY2ztQ-khHM
Learn about Recursive SQL in Advanced SQL for DB2 (24 mins)

there are other youtube vids on this topic too.

This is an often referenced page
http://www.codeproject.com/Articles/83654/Inside-Recursive-CTEs
Inside Recursive CTEs

and of course 'the manual'
http://msdn.microsoft.com/en-us/library/ms186243.aspx
Recursive Queries Using Common Table Expressions
0
 
LVL 92

Author Closing Comment

by:Patrick Matthews
Comment Utility
Perfect, thanks Kevin!

One of these days I will have to wrap my head around how CTEs work.  I can (usually) manage recursive procedures in VBA and VBScript, so I'm not sure what my stumbling block is here :)
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>so I'm not sure what my stumbling block is here
nor I :)
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
You are most welcome, Patrick! I am glad I could help.

Respectfully yours,

Kevin

P.S. @Paul, it is nice to run into you again as I have enjoyed reading your posts by the way - very helpful. Cheers!
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
@Kevin np. happy to help (well, I quite enjoyed trying to anyway). Cheers, Paul
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now