Hello my friends

This is my sql table structure

FK = ID int,
Empnaam varchar(200),
PK = EmpID int

With this table, where i insert values, employees can hire other employees. Now i want to see with a function in visual studio who hired who. I get stuck when a person hired more than 1 person....

This is my stored procedure :


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[ShowHierarchy]
(
@Root int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @EmpID int, @EmpNaam varchar(30)

SET @EmpNaam = (SELECT EmpNaam FROM dbo.Emp WHERE EmpID = @Root)
PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpNaam

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

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

Thanks in advance!

Grtz

Your stored procedure works fine on my machine, it prints a tree view of employees for a given employee. How do you insert records into the Emp table? Do you use the ID column as a pointer to the record's parent record (the one who employs it)? Here is the data I used in my testing:

hi Iori_Jay,

thank you very much for testing my code!! Can you tell me how i call this function in visual studio c#? Let's say i want to know who has hired IORI (so result is: MAI en ATHENA)

I would be so helpfull if you could help me out on this one. i don't see it!! im so close!!

THanks man! Do you have a blog?

Peace out!

SpongeBert:

hi Iori_Jay,

thank you very much for testing my code!! Can you tell me how i call this function in visual studio c#? Let's say i want to know who has hired IORI (so result is: MAI en ATHENA)

I would be so helpfull if you could help me out on this one. i don't see it!! im so close!!

 

THanks man! Do you have a blog?

Peace out!

Seems our comprehension on the hire relation is different. As I understand, the data used in my testing should indicate that IORI hired MAI and ATHENA, not the opposite, so that's what I mean use ID column as ParentID. You can call it as:

EXEC dbo.[ShowHierarchy] 1

As the stored procedure has no output nor returned result set (it just prints the employ tree), you can't get the tree by calling it from application.

Sorry I don't have a blog yet.

Another flaw of this stored procedure is that it uses a recursive algorism, and in SQL Server the max nest level of stored procedure is 32. So the sp can't deal with hierarchy which has more than 32 levels. It's better to implement the tree traversal in code, much easier and better performance.

Hi Iori_Jay,

 Thanks again for your reply.

You're right about the hire relation. My bad. I know there are better ways for solving this problem but i want first to try this way, afterwards i try to improve my code.

 Can you please tell me how i get the output from that stored procedure? so i want to see who IORI hired with result MAI and ATHENA.

 Thanks in advance,

Let me know when you're blog hits the internet!

If you only want to get the employees directly employed by IORI (ignore persons employed by MAI or ATHENA), you can simply use a SELECT:

SELECT EmpNaam FROM dbo.Emp WHERE ID = @Root

Well, we can do a little modification to the stored procedure, but the returned result will still be table-formed result set, you will still face the problem how to get a direct-viewing employ tree from the result set. The only work around I can find is to use a OUTPUT parameter in the stored procedure to host the strings used to be printed in the original sp:

ALTER PROC [dbo].[ShowHierarchy]  @Root int,@outstring VARCHAR(8000) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @EmpID int, @EmpNaam varchar(30)

SET @EmpNaam = (SELECT EmpNaam FROM dbo.Emp WHERE EmpID = @Root)
SET @outstring= @outstring+REPLICATE('-', @@NESTLEVEL * 4) + @EmpNaam +'
'
SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE ID = @Root)

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

DECLARE @outstring varchar(8000)
SET @outstring=''
EXEC [ShowHierarchy] 1,@outstring output
PRINT @outstring

Then in your code you can use a SqlCommand with an OUTPUT parameter to execute the stored procedure and store the output string into a variable. You can refer to: Working with Command Parameters 

Please do remember to initialize the output parameter to an empty string ('') before you use it in the sp.

Iori_Jay, thank you very much. Great explanation!  Keep up the good work