Creating store procedure based on object hierarchy

See more:

SQL-Server

In a database, we store a hierarchy of objects with:
- A table OBJECT_NODE containing a list of objects, each object is identified by a numeric value “ID”
- And a table OBJECT_TREE containing the parent ship between these objects

Create Table OBJECT_NODE ( 
	ID 	int, 
	NAME varchar(255) )	Create Table OBJECT_TREE ( 
	PARENT_ID 	int, 
	CHILD_ID 	int )

For example:

1 ________2________3_______6
| |
| |______7
|
|
|______4________8
| |
| |______9
|
|
|______5 OBJECT_NODE OBJECT_TREE
ID NAME PARENT_ID CHILD_ID

1   N1  1   2
2   N2  2   3
3   N3  2   4
4   N4  2   5
5   N5  3   6
6   N6  3   7
7   N7  4   8
8   N8  4   9
9   N9

For the following 2 questions, please write the procedure for an undefined number of levels in the hierarchy.

1/ Create a SQL Procedure giving all down hierarchy objects of a given node
(in the upper example, all down hierarchy objects of node 2 are 3, 4, 5, 6, 7, 8, 9)

Create Procedure GET_OBJECT_CHILDREN (I_PARENT_ID int)

…

2/ Modify the procedure to give only leaves (objects with no children)
(in the upper example, all leaves objects of node 2 are 5, 6, 7, 8, 9)


Please provide answers to above question based on above hierarchy ..............Plz help me out