Hirerchy Query Help Required

Question

Hi Guys,

I have an oracle background and now working on a project which is SQLServer based so stuck and therefore need your help. I have the following tables which I am using for Menu Management and Tree Population.

Menu

menu_id -------> (PK) int

menu_name ----> varchar(30)

Menu_Template

node_id -----> int

node_name --> varchar(50)

parent_id ----> int

menu_id -----> (FK) int

I have a stored procedure which is like the following

ALTER PROCEDURE [dbo].[sp_Get_Menus]
@p_menu_id int
AS
BEGIN

SELECT node_id, node_name, parent_id, node_type, link, hide, menu_id from Menu_Template WHERE (menu_id = @p_menu_id OR @p_menu_id IS NULL) order by node_id 
END

In oracle we used to add spaces in every child with LEVEL and SPACE but here in SQLServer I dont know how to create the following output in the above stored procedure.

Root
----Administration
--------Site (A)
----------Child (A)

For example purpose I have added "-" but i want spaces here before every child so I can use the query to fill my DropDown Combo in ASp.net control. Please use my above stored procedure and make changes in it.

Thanks