CODEWITHSUNDEEP
sqlsql-serverrecursion
seph
seph

Reputation: 694

How to convert recursive CTE to be usable in SQL Server 2000

I have a recursive CTE-query like this:

;WITH    cte
      AS ( SELECT   e.entryID ,
                    e.bOpen ,
                    e.nextEntryID ,
                    e.entryID AS OriginalentryID
           FROM     entries e
           WHERE    e.bOpen = 1
                    AND e.nextEntryID IS NOT NULL

           UNION ALL

           SELECT   e.entryID ,
                    e.bOpen ,
                    e.nextEntryID ,
                    c.OriginalentryID
           FROM     cte c
                    INNER JOIN entries e ON e.entryID = c.nextEntryID
         )
SELECT  c.entryID ,
        c.OriginalentryID
FROM    cte c
WHERE   bOpen = 0;


Would there be any way to realise this without CTE (i.e. for SQL Server 2000)?
Any hints/ideas are appreciated.

Upvotes: 2

Views: 1121

Answers (1)

Gerardo Lima
Gerardo Lima

Reputation: 6713

For SQL 2000, you can create a table-function that mimics your CTE expression, using a temporary table and a WHILE loop to load it.

The main drawback is that you won't benefit from applying WHERE constraints before returning from the function, but it works if the hierarchy table is small. To lessen the amount of data read before the WHERE clauses apply, you can pass parameters to filter (see comment on code):

CREATE FUNCTION dbo.FnGetEntriesByRoot (@rootId AS BIGINT)
RETURNS @result TABLE (
  entryID           BIGINT PRIMARY KEY,
  bOpen             BIT,
  nextEntryID       BIGINT,
  OriginalentryID   BIGINT
)
AS BEGIN

  -- insert the "root" element
  INSERT @result(entryID, bOpen, nextEntryID, OriginalentryID)
  SELECT e.entryID, e.bOpen, e.nextEntryID, e.entryID
  FROM entries e
  WHERE e.bOpen = 1
  AND (e.entryID = @rootId OR @rootId IS NULL) -- (1) filter condition!
  AND e.nextEntryID IS NOT NULL;

  -- while new items are found, insert into the result table
  WHILE (@@ROWCOUNT > 0) BEGIN
    INSERT @result(entryID, bOpen, nextEntryID, OriginalentryID)
    SELECT e.entryID, e.bOpen, e.nextEntryID, c.OriginalentryID
    FROM @result  c
    JOIN entries  e ON (e.entryID = c.nextEntryID)
    WHERE e.entryID NOT IN (SELECT entryID FROM @result)
  END

  RETURN;
END;

SELECT c.entryID, c.OriginalentryID
FROM dbo.FnGetEntriesByRoot(NULL) c
WHERE c.bOpen = 0;

Upvotes: 2

Related Questions