Friday, March 23, 2012

Noob need help with recursion

I created a table called MyTable with the following fields :

Id int
Name char(200)
Parent int

I also created the following stored procedure named Test, which is supposed to list all records and sub-records.

CREATE PROCEDURE Test
@.Id int
AS

DECLARE @.Name char(200)
DECLARE @.Parent int

DECLARE curLevel CURSOR LOCAL FOR
SELECT * FROM MyTable WHERE Parent = @.Id
OPEN curLevel
FETCH NEXT FROM curLevel INTO @.Id, @.Name, @.Parent
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.Id AS Id, @.Name AS Name, @.Parent AS Parent
EXEC Test @.Id
FETCH NEXT FROM curLevel INTO @.Id, @.Name, @.Parent
END

CLOSE curLevel
DEALLOCATE curLevel
GO

I added a MxDataGrid and DataSourceControl.
SelectCommand property of the DataSourceControl = EXEC Test 0

When I run the aspx page, it only shows 1 record. I tried to change the parameter to 1, 2, 3 but it always shows only 1 record (not the same tho).

Is there something wrong with the stored procedure ?Ok, I made some modifications to make it work properly but there is some limitations. I have to store the results in a temp table. Can I do something similar but without the temp table ?

(Note : I changed some field/table names)


-- ----------------------
-- Fill and select the temp table where the nodes and sub nodes id are stored
-- ----------------------
CREATE PROCEDURE GetNodesAndSubNodes
@.NodeId INT
AS

DELETE FROM TmpNodesAndSubNodes
EXEC GetNodesAndSubNodesRecursive @.NodeId
SELECT * FROM TmpNodesAndSubNodes
GO

-- ----------------------
-- Fill the temp table with the id's of the nodes and it's sub nodes
-- ----------------------
CREATE PROCEDURE GetNodesAndSubNodesRecursive
@.NodeId INT
AS

-- Store the node id into the temp table
INSERT INTO TmpNodesAndSubNodes VALUES(@.NodeId)

-- Declare a local cursor to seek in the nodes table
-- and some variables to retreive the values
DECLARE @.CurrentNodeId INT
DECLARE @.CurrentParentNodeId INT
DECLARE curLevel CURSOR LOCAL FOR
SELECT Id FROM Nodes WHERE ParentNodeId=@.NodeId

-- Fetchs the records and call the recursive method for each node id
OPEN curLevel
FETCH NEXT FROM curLevel INTO @.CurrentNodeId
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC GetNodesAndSubNodesRecursive @.CurrentNodeId
FETCH NEXT FROM curLevel INTO @.CurrentNodeId
END

-- Clean up
CLOSE curLevel
DEALLOCATE curLevel

GO

|||Aaarg :)

I decided to work on a non recursive solution.

No comments:

Post a Comment