July 16, 2003 at 9:09 am
How would I create a query to recurisively return records for example - I have a supplier table and a Folders Table - each supplier belongs to a folder but folders could be nested ie. supplier 1 is linked to folder 3 and folder 3 is nested within 2 and 2 within 1 etc. I need to be able to select folder 1 and get a list of suppliers in folder 1, 2 and 3 - does that make sense ? don't know how to create a stored procedure to achieve this - any ideas
example:
1
|_2 Supplier 1
|_3 Supplier 2
|_4 Supplier 3
By selecting folder 1 - it would return supplier 1,2 and 3
Regards
Brian Corbett
July 17, 2003 at 7:09 am
This link may be usefull for you
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=19663
G.R. Preethiviraj Kulasingham
Chief Technology Officer.
Softlogic Information Systems Limited,
14 De Fonseka Place,
Colombo 05.
Sri Lanka.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
July 17, 2003 at 8:12 am
As the article points out, you can't do this in a single query. You'll have to resort to using a cursor in a stored procedure.
July 17, 2003 at 8:31 am
This looks good - thanks for the advice
The things is - I'm fairly new to Stored Procs and I'm getting a bit lost - is this code easy enough to change to get it to just return a count of the suppliers exists with that particular tree for example
1
|_1.1 Supplier1
|_1.1.1 Supplier2
|_1.2
|_1.2.1 Supplier3
|_1.2.2 Supplier4
|_1.2.2.1 Supplier 5
so for example - 1.1 would return 2
- 1.2 would return 3
- 1.2.2.1 would return 1
Thanks in Advance
Brian Corbett
Senior Useless Person
July 17, 2003 at 8:36 am
quote:
As the article points out, you can't do this in a single query. You'll have to resort to using a cursor in a stored procedure.
Cursors - two words - help me !!
I have an example of something somebody helped me with but it doesn't do it recursivly (or work for that matter)
here it is
CREATE PROCEDURE [CheckFolderTree]
@FolderID INT
AS
DECLARE @TempID INT
DECLARE FolderCursor CURSOR FOR
SELECT [PKID] FROM [ListFolders] WHERE [ListFolders].[ParentFolderID] = @FolderID
OPEN FolderCursor
FETCH NEXT FROM FolderCursor INTO @TempID
WHILE @@FETCH_STATUS = 0
BEGIN
Select FolderID FROM [ListSuppliers] WHERE [FolderID] = @TempID
EXECUTE ('NukeEm(' + @TempID + ')')
FETCH NEXT FROM FolderCursor INTO @TempID
END
DEALLOCATE FolderCursor
GO
July 17, 2003 at 9:52 am
You're well underway of building a complete solution. But I guess you'll have to use a table to store your results (can be a temporary one). If you are sure you only have a fixed number of levels, always, you could just do without having recursion and use a couple of UNION statements.
The idea is to have one procedure to recursively call itself for its 'child' folders and then insert all suppliers that belong to the folder it was called for.
CREATE TABLE #tmpSuppliers (Supplier varchar(50))
CREATE PROCEDURE up_GetSupplier
@FolderID int
AS
--DECLARE temp variable
DECLARE @tmpFolderID int
--DECLARE the cursor
DECLARE FolderCursor CURSOR
FOR SELECT [PKID] FROM [ListFolders] WHERE [ListFolders].[ParentFolderID] = @FolderID
--Open the cursor
OPEN FolderCursor
--Traverse it, recursively calling this procedure
FETCH NEXT FROM FolderCursor INTO @tmpFolderId
WHILE @@FETCH_STATUS = 0
BEGIN
exec UP_GetSupplier @tmpFolderID
FETCH NEXT FROM FolderCursor INTO @tmpFolderId
END
--Now insert the suppliers of this level
INSERT INTO #tmpSuppliers
SELECT Supplier FROM Suppliers WHERE <Supplier belongs to Folder>
END
--Execute the procedure for folder 1
EXEC UP_GetSupplier 1
--Select the results
SELECT Supplier FROM #tmpSuppliers
You'll have to make sure the WHERE clause of the 'INSERT INTO ... SELECT' stuff is correct, of course.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply