MSSQL Server - Stored Procedure - Rekursive aufrufe.

[generic]

moinsen,

ich habe eine baumstruktur in eine datenbank tabelle.
nun versuche ich eine procedure zu schreiben welche mir die daten aufgeloest zurueck gibt.
als parameter möchte ich den startknoten mitgeben.

diese seite hatte auch mal das problem: http://www.entwicklerforum.de/webx?5...V3.0@.1dd05441

nur leider unter interbase. mit t-sql ist das voll der krampf. kaum erfahrung damit und schlecht dokumentier ist es auch noch.

optional: cool waere wenn die procedure sich nicht tod laeuft wenn irdendwo ein cirularer verweise drin ist.

für hilfe und tipps waere ich dankbar.

[Leuselator]

was meinst Du mit "aufgelöst zurückgibt" ? (Beispiel wäre nicht schlecht)
Gruß

[generic]

Code:

root - sub1 - sub 2
     |      
     + sub3
         |
         +- sub4
             |
             + sub5

select * from meineProcedure(sub3)
----------------------------------
sub4
sub5
----------------------------------

oder

select * from meineProcedure(sub3)
----------------------------------
sub3
sub4
sub5
----------------------------------

jenachdem was sich leichter implementieren laesst.


hab jetzt noch das hier gefunden, da lese ich mich jetzt zusaetzlich noch durch.
Google - Newsgroup - Artikel

[Leuselator]

SQL-Code:

-- Tabelle anlegen:
CREATE Table Sippe ( idSippe  int identity(1,1) NOT NULL
                   , idParent int                   NULL
                   , Vorname  varchar(25)           NULL
                   , Name     varchar(25)           NULL)
GO

CREATE PROCEDURE pp_GetChildren
( @Root          int                 -- die Id des gesuchten Satzes
, @MaxTiefe      int          =   10 -- Suchtiefe (Standardwert 10)

, @ResultTabelle varchar(255) = NULL -- bei manuellem Aufruf weglassen!
, @ResultEbene   int          = NULL -- bei manuellem Aufruf weglassen!
)
AS
BEGIN
  DECLARE @TmpTable  varchar(255)
        , @AktId     int
        , @AktEbene  int
        , @i         int
        , @SQL       nvarchar(2000)
        , @DerCursor CURSOR

  SET @AktEbene = @ResultEbene+1 -- Ebene für Rekursiven Aufruf erhöhen
  SET @MaxTiefe = @MaxTiefe   -1 -- Restebenen veringern  
  IF @ResultEbene IS NULL SET @ResultEbene = 0
  IF @ResultTabelle IS NULL BEGIN -- dann müssen wir die temporäre
                                  -- Tabelle erst noch anlegen
                                  -- damit sich verschiedene Client-
                                  -- anwendungen beim Aufruf nicht in's
                                  -- Gehege kommen, basteln wir uns
                                  -- einen noch nicht vorhandenen Namen
    SET @i = 0 
    SET @TmpTable = '##TmpGetChld_'+convert(varchar,@i)
    WHILE object_id('tempdb..'+@TmpTable, 'U') is not null BEGIN
      -- wenn die temporäre Tabelle schon vorhanden ist, erhöhen wir @i
      -- solange, bis wir einen Tabellennamen erwischen, der noch nicht 
      -- benutzt ist
      SET @i = @i+1
      SET @TmpTable = '##TmpGetChld_'+convert(varchar,@i)
    END
    -- nun legen wir die Tabelle an
    EXEC('CREATE TABLE '+@TmpTable+' 
               ( NR     int identity(1,1) NOT NULL
               , ItemId int               NOT NULL
               , Ebene  int               NOT NULL
               )')
    -- wenn Die Root im Ergebnis auftauchen soll, müssen wir sie hier
    -- in die temporäre Tabelle einfügen: (dazu die nächsten 10 Zeilen 
    -- "entkommentieren":
--    SET @SQL = '
--    INSERT INTO '+@TmpTable+'
--              ( ItemId
--              , Ebene
--              ) 
--         VALUES 
--              ( @AktId
--              , @ResultEbene
--              )'
--    EXEC sp_ExecuteSql @SQL,N'@AktId int, @AktEbene int',@AktId,@AktEbene 
  END ELSE BEGIN -- Tabellenname wurde übergeben - also nicht anlegen
    SET @TmpTable = @ResultTabelle 
  END

  -- dynamischen Cursor für Suche in aktueller Ebene deklarieren:
  SET @SQL = 'SET @DerCursor = CURSOR FOR
                               SELECT idSippe
                                 FROM Sippe
                                WHERE idParent = @Root'
  EXEC sp_ExecuteSql @SQL, N'@DerCursor CURSOR OUTPUT, @Root int', @DerCursor OUTPUT, @Root

  OPEN @DerCursor
  FETCH NEXT
   FROM @DerCursor
   INTO @AktId
  WHILE @@Fetch_Status = 0 BEGIN -- alle direkten Nachkommen 
                                 -- durchgehen und in die Temporäre
                                 -- Tabelle einfügen
    SET @SQL = '
    INSERT INTO '+@TmpTable+'
              ( ItemId
              , Ebene
              ) 
         VALUES 
              ( @AktId
              , @ResultEbene
              )'
    EXEC sp_ExecuteSql @SQL,N'@AktId int, @AktEbene int',@AktId,@AktEbene 
 
    IF @MaxTiefe > 0 BEGIN -- wenn noch weitergesucht werden soll, 
      EXEC pp_GetChildren  -- ist das der Rekursive Aufruf, um 
           @AktId          -- nach Kindern des aktuellen Kindes zu suchen
         , @MaxTiefe   
         , @TmpTable
         , @AktEbene
    END
    -- nächstes Kind holen:
    FETCH NEXT
     FROM @DerCursor
     INTO @AktId
  END
  -- dyn. Cursor freigeben:
       CLOSE @DerCursor
  DEALLOCATE @DerCursor
  IF @ResultEbene = 0 BEGIN -- nur ausführen, wenn wir aus der Rekursion
                            -- wieder "aufgetaucht" und in Ebene 0 sind
    SET @SQL = '
         Select A.idSippe
              , A.idParent
              , A.Vorname
              , A.Name
              , B.Ebene
           FROM Sippe A
     INNER JOIN '+@TmpTable+' B
             ON B.ItemId = A.idSippe
       ORDER BY B.NR'
    -- Liefert das letztendliche Resultset:
    EXEC (@SQL)
    und temporäre Tabelle freigeben:
    if object_id('tempdb..'+@TmpTable, 'U') is not null EXEC('DROP TABLE '+@TmpTable)
  END
END
GO

füge ein paar Daten in die Tabelle "Sippe" ein
und setze anschließend im QueryAnalyzer ab:
EXEC pp_GetChildren 1,5 -- RootId & SuchTiefe sollte funktionieren...
Gruß
PS: "pp_" ist willkürlich und steht bei mir für "private procedure"

[generic]

danke für die sehr ausführliche lösung.
einen ähnlichen ansatz mit einer temp-tabelle habe ich hier noch gefunden:

http://authors.aspalliance.com/Maman...ursive&print=y

hmm, performance massig überzeugt mich das alles noch nicht so.
ich suche nochmal weiter.

[generic]

ich schliesse fuer mich die frage ab.

für die die noch an einer lösung interesiert sind:

http://vyaskn.tripod.com/hierarchies..._databases.htm
und
http://support.microsoft.com/default...roduct=sql2k#2