It lives! It lives! (RE: Recursion)

It lives! It lives! (RE: Recursion)

Post by Joseph Clar » Sun, 06 Feb 2000 04:00:00



Hi all,
Just wanted to let anybody who is interested know that I've figured out
how to actually make a recursive algorithm work in Transact-SQL.

Problem: I have a set of product categories nested within one another,
each with an ID and a parentID (null parentID's are top-level
categories). I could easily generate a list of subcategories given a
category ID, but what I couldn't figure out is how to generate a list of
ALL subcategories, and subcategories of subcategories, no matter how
many levels deep. It turns out the solution was to place all the
subcategories in a temporary table, then loop until the count of that
table was zero, adding sub-subcategories for each item down the list.
Check out the code:

Alter Procedure spAllSubcats

As
SET NOCOUNT ON


   CREATE TABLE #stack (
     ID         SMALLINT,
     name       VARCHAR(50)
     )

   CREATE TABLE #output (
     ID         SMALLINT,
     name       VARCHAR(50)
     )

   INSERT INTO #stack (ID, name)
    (SELECT ID, name
     FROM Categories

   WHILE (SELECT COUNT(*) FROM #stack) > 0
   BEGIN

      FROM #stack

      INSERT INTO #stack (ID, name)
      (SELECT ID, name
       FROM Categories

      INSERT INTO #output (ID, name)
      (SELECT ID, name
       FROM #stack

      DELETE FROM #stack

      END

      SELECT * FROM #output

 
 
 

1. Call 1-800-856-2469, LIVE LIVE LIVE 809-474-7588 code4389

18+, 24hours, rates as low as $0.38/min

Hot, Young women want it NOW ----011-592-247-681
Gay, Bi, Bi-curious guys at -----809-474-7604
************************************************
************************************************
************************************************
************************************************

comp.databases.pick

2. SQL 7.0 Monitoring

3. Managing a live display of a live database table...

4. 7.3RC2 createlang error

5. PostgreSQL in Comparison to mySQL

6. MS_SQL 2000 is there a end of life on it and is there a new version

7. dbdsn tool question

8. Live Tables in Linked Server

9. Error msg: Rowset built over a live datafeed

10. Free SQL Tutorial w/ live practice database - on-line/ web based