Query to recursively climb down a tree...

please replace the Fields and Table names.

Imran

it will show you result like this

---President
------Vice President
---------CEO
---------CTO
------------Group Project Manager
---------------Project Manager 1
------------------Team Leader 1
---------------------Software Engineer 1
---------------------Software Engineer 2
------------------Test Lead 1
---------------------Tester 1
---------------------Tester 2
---------------Project Manager 2
------------------Team Leader 2
---------------------Software Engineer 3
---------------------Software Engineer 4


you can modify the procedure as you need, or tell us if you have any problem.

Imran

hi,
will using PRINT in the sql query as shown above, return the output data into the result-set which I use in either Java or any other application?

thanks,
Sandesh

The solution provided by " imrancs " above is great, thanks imran, but the problem is how do I return the values printed, back to the result set. Also i have a problem that a few records are repeating something like this...

---President
------Vice President
---------CEO
---------CTO
------------Group Project Manager
---------------Project Manager 1
------------------Team Leader 1
---------------------Software Engineer 1
---------------------Software Engineer 2
------------------Test Lead 1
------------Group Project Manager

Is there any one who wants to share a lil bit of points with imran...???

thanks,
Sandesh

>>return the output data into the result-set which I use in either Java or any other application.

Create a temp table inside the procedure and instead of PRINT insert the data to that table and then from another SP select the data from the temp table, like

Create Procedure GetResult
  @Id   int
AS

 -- here first create the temp table
  Create table #temp(................)
 -- then call the procedure
 Exec  ShowHierarchy @Id

  Select * from #temp
Go

Oops, #temp table created outside the ShowHierarchy procedure may not be available in ShowHierarchy procedure, you need to have a global temp table just change #temp to ##temp

Imran

>>Also i have a problem that a few records are repeating something like this...

try to print the Ids alongwith the names and see if repeating records has the same Ids too, there may be a chance to have records of duplicate name with different Ids (just my notion).


Imran

hi imran,
great help from you... but last doubt about the code do you mean this..

CREATE PROC dbo.ShowHierarchy
(
     @Root int
)
AS
BEGIN
     SET NOCOUNT ON
     DECLARE @EmpID int, @EmpName varchar(30)
     DECLARE @tempTable TABLE(EmpName varchar(30).....)

insert into @tempTable    
SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root

     SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root)

     WHILE @EmpID IS NOT NULL
     BEGIN
          EXEC dbo.ShowHierarchy @EmpID
          SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root AND EmpID > @EmpID)
     END
END



then another proc I write.

Create Procedure GetResult
  @Id   int
AS

 -- here first create the temp table
  Create table ##tempTable(EmpName varchar(30).....)
 Exec  ShowHierarchy @Id

  Select * from ##tempTable
Go



but this doesnt work, can u for the last time rewrite this code for me.

thanks,
Sandesh

one final time..
but here it gives me a message stating table already created, what do i do if i need to run it again and again??

thanks,
Sandesh

do you have created the table in GetList or ShowHierarchy


Imran

place this before creating table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[##temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[##temp]


Imran

imran...
"select * from dbo.sysobjects where id = object_id(N'[dbo].[##temp]') "

where temp is my Created table, this statement returns nothing!!! although

"select * from ##temp " returns the proper data temporarily stored !?

thanks
Sandesh

by the way i have created the table in the GetList Procedure

Oh, now i got it, the procedures work correctly without having to drop the table each time by actually changing the temp table from ##temp to #temp everywhere.

Ill give you my full 150 points to you, for an extra 25 points can you tell me how can I access tables that is in a different database as well as a different server alltogether. Without actually creating a View.

Thanks,
Sandesh

ok.. thanks mite,

It was a Great learning experience for me. Hope to get some more valuable suggestions from you in the future.

Thanks,
Sandesh

My pleasure :o)

and thanks for the points

Imran