Execute Method & Expanding Hierarchies

[JMorrell]

I have a stored procedure on my SQL Server that can capture a downstream
expanding hierarchy of my employee table. It captures all employees
belonging to a supervisor, and any supervisors/employees under that
supervisor. In SQL Analyzer, I have a select statement with a variable that
when executed, uses the stored procedure to list those names. The code (from
Itzik Ben-Gan at SQL Server Magazine) for both is below:

CREATE FUNCTION ufn_GetSubtree
(
@supervisorid AS int
)
RETURNS @tree table
(
employeeid int NOT NULL,
supervisorid int NULL,
lname varchar(25) NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS

BEGIN

DECLARE @lvl AS int, @path AS varchar(900)
SELECT @lvl = 0, @path = '.'

INSERT INTO @tree
SELECT employeeid, supervisorid, lname,
@lvl, '.' + CAST(employeeid AS varchar(10)) + '.'
FROM tblemp
WHERE employeeid = @supervisorid

WHILE @@ROWCOUNT > 0
BEGIN
SET @lvl = @lvl + 1

INSERT INTO @tree
SELECT E.employeeid, E.supervisorid, E.lname,
@lvl, T.path + CAST(E.employeeid AS varchar(10)) + '.'
FROM tblEmp AS E JOIN @tree AS T
ON E.supervisorid = T.employeeid AND T.lvl = @lvl - 1
END

RETURN

END

-------------------------
select * from ufn_getsubtree(00285) as s
where not exists(select * from tblemp as e
where e.supervisorid = s.employeeid)

Everything is working fine on the server side. Now I want to be able to use
the sp and the SQL statement in an Access routine. Going on the assumption
that an Execute (ADO Command) can return a recordset, I want to use that
recordset to populate a list box on a form (this is then used to run an
existing query which feeds a report). Can someone guide me through the steps
necessary to accomplish this?

tia,

 

[solex]

John,

I do not believe that you can call a stored function (I will admit I have
never tried). You may want to consider changing the function to a procedure
that simply selects * from your temprorary cursor. Then you can us ADO to
execute a command object the is set to a recordset object.

Dan