How can I Retrieve a hierarchical Recordset from a MSSQL stored procedure?
Posted on 2007-08-06
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
SET NOCOUNT ON
DECLARE @MaKurz varchar(6), @MaName varchar(30), @MaStatus int
SET @MaKurz = (SELECT MIN(MaKurz) FROM dbo.T250_Ma WHERE Vorgesetzter = @Root)
WHILE @MaKurz IS NOT NULL
EXEC dbo.ShowHierarchy @MaKurz
SET @MaKurz = (SELECT MIN(MaKurz) FROM dbo.T250_Ma WHERE Vorgesetzter = @Root AND MaKurz > @MaKurz)
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
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.