# Multiple round trip to server (worst case)
- Make an SP which returns all parent/child (whatever required) IDs
- From code call SP recursively untill you reach the end.
# Single round trip to server (thats what a better option - again not the best one)
- Make an SP which returns everything from the table.
- Just make your recurssion process in Business logic (.NET/Java) rather than T-SQL
# Without recursion (hopefully, the best)
Let's dig it out, Here is the story. I tried to solve the problem in most effective way using many ways including the above two. But no way could end up into success. I was looking for some logical way at SP end. Below is the table structure and the SP. SP comments would help you to understand it well. Just go through it, it's very easy. Also I would appreciate if you have the solution in some other way.
CREATE TABLE [_Test_Hierarchy] (
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[ParentID] [numeric](10, 0) NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
CREATE PROCEDURE Test_Expand -- Creates an stored procedure
@current NUMERIC -- The ID of which all childs are required
DECLARE @LinesUpdated INT, @Level INT
SET NOCOUNT ON
SET @LinesUpdated = 1 -- It holds the value indicating number of rows inserted into temp table.
SET @Level = 1 -- used to identify the nodes for which we have already added childs.
CREATE TABLE #stack -- temporary table, you can add more columns as per your comfort.
INSERT INTO #stack -- Insert the current node and set it's level to 1.
VALUES (@current, @Level)
WHILE @LinesUpdated > 0
SET @Level = @Level + 1 -- Every loop iteratation increast one level.
-- Here we need to focus on two criteria while shortlisting.
-- 1. Check all child nodes of the parents already stored into temp table - #stack
-- 2. A node already in the list, must not be repetated. That is why we kept Lvl = @Level - 1 condition. It always search for fresh nodes.
INSERT INTO #stack
SELECT t1.ID, @Level
(SELECT * FROM #stack WHERE Lvl = @Level - 1) s1
WHERE 1 = 1
AND s1.ID = t1.ParentID
SET @LinesUpdated = @@ROWCOUNT -- @@ROWCOUNT always holds last count of selection. Terminate the loop when no row added.
-- Its very simpe, you must be knowing...
SELECT t1.ID, t1.Name
WHERE 1 = 1
AND s1.ID = t1.ID