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)
|||Aaarg :)
-- ----------------------
-- Fill and select the temp table where the nodes and sub nodes id are stored
-- ----------------------
CREATE PROCEDURE GetNodesAndSubNodes
@.NodeId INT
ASDELETE 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 curLevelGO
I decided to work on a non recursive solution.
