Recursive-like queries in SQL Server

Question

I'm trying to figure out how to perform what would essentially be a recursive query in SQL. I've got two tables;

TABLE Object(
id INT NOT NULL PRIMARY KEY
)

TABLE ObjectDependency(
object_id INT,
dependency_id INT,
FOREIGN KEY(object_id)     REFERENCES Object(id)
FOREIGN KEY(dependency_id) REFERENCES Object(id)
)

I want to write a stored procedure that will take an object id and spit out all of the objects dependancies (something like this, but also any found dependency's dependency.

SELECT id, ObjectDependency.id FROM Object
JOIN ObjectDependency ON object_id = id

The system is set up in such a way that there are no cyclical dependancies, but I'm kind of lost on how I would manage to loop all results into one stored procedure.

To this, there are different solutions for different database products (Oracle/Mysql/PostgreSQL/MS SQL/...). Which one are you using? — The Nail, Dec 04 '11 at 00:58
SQL Server, sorry, I'm a little lost in the world of databases. — user308926, Dec 04 '11 at 01:03
See http://stackoverflow.com/questions/959804/simulation-of-connect-by-prior-of-oracle-in-sql-server — xQbert, Dec 04 '11 at 01:11

marc_s · Answer 1 · 2011-12-04T10:08:09.433

If you're on SQL Server 2005 or newer, you can use a recursive CTE (Common Table Expression) to do this (see MSDN Books Online docs for more details).

Basically, it's an "inline view" - a view that only exists for the next statement. One version of the CTE is designed specifically to handle recursive scenarios.

It looks something like this:

CREATE PROCEDURE dbo.RecurseObjects @ObjectID INT
AS BEGIN
  WITH ObjectCTE AS 
  (
      -- set the anchor - select the object defined
      SELECT o.id AS 'ID', CAST(NULL AS INT) AS 'ParentID', 1 AS 'Level'
      FROM dbo.Object o
      WHERE o.id = @ObjectID

      -- add recursion
      UNION ALL

      SELECT o2.id AS 'ID', cte.id AS 'ParentID', cte.Level + 1 AS 'Level'
      FROM dbo.Object o2
      INNER JOIN dbo.ObjectDependency od ON od.dependency_id = o2.id
      INNER JOIN ObjectCTE cte ON cte.id = od.object_id
  )
  SELECT *
  FROM ObjectCTE
END

So this recursive CTE is run in stages:

the first "run" sets the anchor, that is, the first SELECT is executed and the results are stored in a temporary result set
then, the recursion is handled: the second select is run, basically selecting all rows that are dependent from the row(s) selected in the first run - all those that are linked like this:
```
object.id --> objectdepedency.dependecy_id 
              objectdepedency.object_id --> "parent" object.id
```

This second step is repeated over and over, until no more additional rows are retrieved - then the result set is returned.

Recursive-like queries in SQL Server

1 Answers1