help with recursive stored procedure

[impulse24]

I have the folling stored procedure:

CREATE PROCEDURE gouplevels (@current char(20)) as
SET NOCOUNT ON
DECLARE @level int, @line char(20),@foldname varchar(255)
CREATE TABLE #stack (item char(20), level int,foldname varchar(255))
INSERT INTO #stack VALUES (@current, 1,@foldname)
SELECT @level = 1

WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @current = item,@foldname=foldname
FROM #stack
WHERE level = @level
SELECT @line = space(@level - 1) + @current
PRINT @foldname
DELETE FROM #stack
WHERE level = @level
AND item = @current
INSERT #stack
SELECT parentid, @level + 1,name
FROM toc
WHERE tocid = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE
GO


I am supplying the current and it returns the recursive tree in the query analyzer, but I dont know how to access the same results in ADO.

I have setup a recordset, and call the stored procedure with the command object, but I am not seeing anything populated in the recordset

Below is an example of the data:

Tocid Parentid Name
-------------------------
1 0 dave
2 1 john
3 2 mark
4 3 steve

so running the stored procedure using tocid 4 produces the following in query analyzer:

mark
john
dave

The true end result I want in my recordset is mark|john|dave, but since I only have the print statement in my stored procedure, im not not sure how to access that data.

Any help would me much appreciated

 

[dGrouse]

Don't use PRINT statements to "return records"; ADO certainly won't take your PRINT statements as a record set. You need to return a real record set. Consider populating a table variable, and then spitting out the contents at the end.

Jeff Prenevost
BI Consultant
Ann Arbor, MI