March 4, 2009 at 3:20 pm
Hi folks,
I have a table:
ID, ParentID, Desp
All the records in the table can be traced back to a root, this table is used to create a treeview
I need to write a sp to do:
given any Desp, get matched record, pull out all the records that are associated with it, its parent, parent's parent, ... and so on until it gets to the root, and then display the result in a table, starts from root, child, child's child, .... until the record.
Can anyone help me on how to write such a sp?
Thanks in advance.
March 4, 2009 at 3:37 pm
Sample:
SET NOCOUNT ON
DECLARE @tree TABLE (id INT, parent INT, desp VARCHAR(100))
INSERT INTO @tree VALUES (1, NULL, 'a')
INSERT INTO @tree VALUES (2, 1, 'b')
INSERT INTO @tree VALUES (3, 2, 'c')
INSERT INTO @tree VALUES (3, NULL, 'd')
INSERT INTO @tree VALUES (4, 3, 'e')
INSERT INTO @tree VALUES (5, 3, 'f')
DECLARE @desp VARCHAR(100)
DECLARE @decendant INT
DECLARE @parent INT
DECLARE @path VARCHAR(100)
-- Configure the desp
SET @desp = 'c'
SELECT @parent = parent, @path = @desp
FROM @tree WHERE desp = @desp
WHILE (@parent IS NOT NULL)
BEGIN
SET @decendant = @parent
SET @parent = NULL
SELECT @parent = parent, @path = desp + '->' + @path FROM @tree WHERE id = @decendant
END
PRINT @path
March 4, 2009 at 4:06 pm
Thanks for your quick response, but I need table in the return.
March 4, 2009 at 4:20 pm
Hi
So just put the results into a table and return it:
SET NOCOUNT ON
DECLARE @tree TABLE (id INT, parent INT, desp VARCHAR(100))
INSERT INTO @tree VALUES (1, NULL, 'a')
INSERT INTO @tree VALUES (2, 1, 'b')
INSERT INTO @tree VALUES (3, 2, 'c')
INSERT INTO @tree VALUES (3, NULL, 'd')
INSERT INTO @tree VALUES (4, 3, 'e')
INSERT INTO @tree VALUES (5, 3, 'f')
DECLARE @result TABLE (incr_id INT IDENTITY, id INT, parent INT, desp VARCHAR(100))
DECLARE @desp VARCHAR(100)
DECLARE @prev_id INT
DECLARE @parent INT
-- Configure the desp
SET @desp = 'c'
INSERT INTO @result
SELECT * FROM @tree WHERE desp = @desp
WHILE (1 = 1)
BEGIN
SET @prev_id = @@IDENTITY
SELECT @parent = parent FROM @result WHERE incr_id = @prev_id
INSERT INTO @result
SELECT *
FROM @tree
WHERE id = @parent
IF (@@IDENTITY = @prev_id)
BREAK
END
SELECT * FROM @result ORDER BY parent
Greets
Flo
March 4, 2009 at 5:59 pm
Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply