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
pone
Member
35 Points
7 Posts
Noob need help with recursion
Aug 07, 2003 08:18 AM|LINK
pone
Member
35 Points
7 Posts
Re: Noob need help with recursion
Aug 11, 2003 09:58 AM|LINK
-- ------------------------------------------------------------------------ -- 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 GOpone
Member
35 Points
7 Posts
Re: Noob need help with recursion
Aug 11, 2003 12:54 PM|LINK