SQL 2008: How to recursive Procedure for parentNavigation?

13 Dec 2008 11:04 AM

Andreas Klemt

Hello,

how can I do a recursive Procedure in SQL 2008 to get all parentNavigation
Nodes for this example?

navigaionID   parentNavigationID   Level
------------------------------------------
1                  0                                0
2                  0                                0
3                  0                                0
4                  2                                1
5                  0                                0
6                  4                                2
7                  6                                3

I just want a function where I enter the navigationID and then get all
partenNavigationIDs like this

MyProcedureGetAllParentNodes @navigationID=7
Result:
navigaionID    Level
--------------------
6                    3
4                    2
2                    1

Many, many thanks in advance for any help!

Andreas

13 Dec 2008 3:16 PM

Plamen Ratchev

The following will give you the requested result set:

CREATE TABLE Navigation (
navigationID INT PRIMARY KEY,
parentNavigationID INT,
Level INT);

INSERT INTO Navigation VALUES
(1, 0, 0),
(2, 0, 0),
(3, 0, 0),
(4, 2, 1),
(5, 0, 0),
(6, 4, 2),
(7, 6, 3);

GO

CREATE PROCEDURE MyProcedureGetAllParentNodes
@navigationID INT
AS
WITH NavigationCTE
AS
(SELECT navigationID, parentNavigationID, Level
   FROM Navigation
   WHERE navigationID = @navigationID
   UNION ALL
   SELECT N.navigationID, N.parentNavigationID, N.Level
   FROM Navigation AS N
   JOIN NavigationCTE AS C
     ON N.navigationID = C.parentNavigationID
   WHERE N.parentNavigationID <> 0)
SELECT parentNavigationID AS navigationID, Level
FROM NavigationCTE;

GO

EXEC MyProcedureGetAllParentNodes @navigationID = 7;

/*

Results:

navigationID Level
------------ -----------
6            3
4            2
2            1

*/

--
Plamen Ratchev
http://www.SQLStudio.com

17 Dec 2008 11:03 AM

Andreas Klemt

Hi Plamen,

thank you very much! It helped me.

Regards
Andres

Show quoteHide quote

"Plamen Ratchev" <Pla***@SQLStudio.com> schrieb im Newsbeitrag
news:C8adneQ_0MltTN7UnZ2dnUVZ_sfinZ2d@speakeasy.net...
> The following will give you the requested result set:
>
> CREATE TABLE Navigation (
> navigationID INT PRIMARY KEY,
> parentNavigationID INT,
> Level INT);
>
> INSERT INTO Navigation VALUES
> (1, 0, 0),
> (2, 0, 0),
> (3, 0, 0),
> (4, 2, 1),
> (5, 0, 0),
> (6, 4, 2),
> (7, 6, 3);
>
> GO
>
> CREATE PROCEDURE MyProcedureGetAllParentNodes
> @navigationID INT
> AS
> WITH NavigationCTE
> AS
> (SELECT navigationID, parentNavigationID, Level
>   FROM Navigation
>   WHERE navigationID = @navigationID
>   UNION ALL
>   SELECT N.navigationID, N.parentNavigationID, N.Level
>   FROM Navigation AS N
>   JOIN NavigationCTE AS C
>     ON N.navigationID = C.parentNavigationID
>   WHERE N.parentNavigationID <> 0)
> SELECT parentNavigationID AS navigationID, Level
> FROM NavigationCTE;
>
> GO
>
> EXEC MyProcedureGetAllParentNodes @navigationID = 7;
>
> /*
>
> Results:
>
> navigationID Level
> ------------ -----------
> 6            3
> 4            2
> 2            1
>
> */
>
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com