How can I Retrieve a hierarchical Recordset from a MSSQL stored procedure?

I try to generate a hierarchical Tree with a stored procedure an pass the result to a ADO Recordset.
The Database is quite easy, each Employee has a ID (MaKurz), Name (MaName), Status (MaStatus), Deputy (MaStv) and a Chef (MaChef). What I need is a recordset whith all Emloyees (and sub-employees) of a specific manager.
I've found a answere here in EE, which was partially helpful. Here is my Version which is still working with Query Analyzer but gives just the key (MaKurz) and only in Query Analyzer.

CREATE    PROC dbo.ShowHierarchy
(
      @Root varchar(6)
)
AS
BEGIN
      SET NOCOUNT ON
      DECLARE @MaKurz varchar(6), @MaName varchar(30), @MaStatus int

      PRINT @Root    

      SET @MaKurz = (SELECT MIN(MaKurz) FROM dbo.T250_Ma WHERE Vorgesetzter = @Root)

      WHILE @MaKurz IS NOT NULL
      BEGIN
            EXEC dbo.ShowHierarchy @MaKurz
            SET @MaKurz = (SELECT MIN(MaKurz) FROM dbo.T250_Ma WHERE Vorgesetzter = @Root AND MaKurz > @MaKurz)

      END
END
GO


Well, the problem is that I can't receive the List in a VB ADO Recordset.

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "ShowHierarchy"
cmd.Parameters.Append cmd.CreateParameter("@Root", adBSTR, adParamInput, , "xyz")
Set cmd.ActiveConnection = db

Dim rst As ADODB.Recordset
Set rst = cmd.Execute
MsgBox rst.RecordCount
rst.Close

The second problem is that I need also the other informations like Name and Status of the Employees, but for this I could start a new Query in ADO.
jpsteinerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
ok, let's create the following function:

CREATE    FUNCTION dbo.GetHierarchy
(
      @Root varchar(6)
)
RETURNS @T TABLE ( MaKurz  varchar(6) )
AS
BEGIN
 DECLARE @MaKurz Varchar(6)
 DECLARE @I TABLE ( MaKurz varchar(6))

      SET NOCOUNT ON
      INSERT INTO @I ( MaKurz ) SELECT MaKurz FROM dbo.T250_Ma WHERE Vorgesetzter = @Root

      SELECT TOP 1 @MaKurz = MaMurz FROM @I
      WHILE @@ROWCOUNT > 0
      BEGIN
          INSERT INTO @T ( MaKurz ) VALUES ( @MaKurz )
          INSERT INTO @T ( MaKurz ) SELECT MaKurz FROM dbo.GetHierarchy(@MaKurz )

          DELETE @I WHERE MaMurz  = @MaKurz
          SELECT TOP 1 @MaKurz = MaMurz FROM @I
      END

   RETURN
END


and you can use it like this:
select * from dbo.GetHierarchy('abc') l

and eventually, join back to the table to get more information:

select t.*
from dbo.GetHierarchy('abc') l
join dbo.T250_Ma t
where t.MaKurz = l.MaKurz

which you could do in a procedure or another function, which you would then call in your vb code





0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Well, the problem is that I can't receive the List in a VB ADO Recordset.
you can, if you check the rst.NextRecordset  object (recurring), each SELECT of the procedure will generate an additional .NextRecordset object.


now, what version of MS SQL Server are you using?
0
 
jpsteinerAuthor Commented:
It is MSSQL V2k
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
jpsteinerAuthor Commented:
angellll:
When I try to create the function I get the following Errormessage:
Invalid column name 'MaMurz'
Invalid use of 'SET OPTION ON' within a function.

should I replace 'MaMurz' with 'MaKurz' (3 times)
should I remove 'SET NOCOUNT ON'?
Thank you for supporting!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>should I replace 'MaMurz' with 'MaKurz' (3 times)
>should I remove 'SET NOCOUNT ON'?
yes, sorry for those errors...
0
 
jpsteinerAuthor Commented:
the correct vb syntax is:
select * from dbo.GetHierarchy('abc') as l join T250_Ma as t on t.MaKurz = l.MaKurz

Thank you anyway!
0
All Courses

From novice to tech pro — start learning today.