Simulation of CONNECT BY PRIOR of Oracle in SQL Server

by

The SQL standard way to implement recursive queries, as implemented e.g. by IBM DB2 and SQL Server, is the WITH clause. See this article for one example of translating a CONNECT BY into a WITH (technically a recursive CTE) — the example is for DB2 but I believe it will work on SQL Server as well.

Edit: apparently the original querant requires a specific example, here’s one from the IBM site whose URL I already gave. Given a table:

CREATE TABLE emp(empid  INTEGER NOT NULL PRIMARY KEY,
                 name   VARCHAR(10),
                 salary DECIMAL(9, 2),
                 mgrid  INTEGER);

where mgrid references an employee’s manager’s empid, the task is, get the names of everybody who reports directly or indirectly to Joan. In Oracle, that’s a simple CONNECT:

SELECT name 
  FROM emp
  START WITH name="Joan"
  CONNECT BY PRIOR empid = mgrid

In SQL Server, IBM DB2, or PostgreSQL 8.4 (as well as in the SQL standard, for what that’s worth;-), the perfectly equivalent solution is instead a recursive query (more complex syntax, but, actually, even more power and flexibility):

WITH n(empid, name) AS 
   (SELECT empid, name 
    FROM emp
    WHERE name="Joan"
        UNION ALL
    SELECT nplus1.empid, nplus1.name 
    FROM emp as nplus1, n
    WHERE n.empid = nplus1.mgrid)
SELECT name FROM n

Oracle’s START WITH clause becomes the first nested SELECT, the base case of the recursion, to be UNIONed with the recursive part which is just another SELECT.

SQL Server’s specific flavor of WITH is of course documented on MSDN, which also gives guidelines and limitations for using this keyword, as well as several examples.

More Related Contents:

  1. Check if table exists in SQL Server
  2. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  3. What are the main performance differences between varchar and nvarchar SQL Server data types?
  4. Datatype for storing ip address in SQL Server
  5. best way to convert and validate a date string
  6. Why use the INCLUDE clause when creating an index?
  7. Creating audit triggers in SQL Server
  8. Regular Expressions in SQL Server servers?
  9. How do you specify a different port number in SQL Management Studio?
  10. Diagnosing Deadlocks in SQL Server 2005
  11. Disadvantages of MARS (Multiple Active Result Sets)?
  12. Aggregate bitwise-OR in a subquery
  13. Check if a temporary table exists and delete if it exists before creating a temporary table
  14. Parse XML in SQL Server
  15. Drop primary key using script in SQL Server database [duplicate]
  16. sql use statement with variable
  17. Decimal values in SQL for dividing results
  18. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  19. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  20. How to store directory / hierarchy / tree structure in the database?
  21. Date range overlapping check constraint
  22. INFORMATION_SCHEMA vs sysobjects
  23. Bulk insert fixed width fields
  24. Does query plan optimizer works well with joined/filtered table-valued functions?
  25. “Prevent saving changes that require the table to be re-created” negative effects
  26. Get row where datetime column = today
  27. Convert UTC Milliseconds to DATETIME in SQL server
  28. Table Valued Function Killing My Query Performance
  29. Full text catalog/index search for %book%
  30. @@ERROR and/or TRY – CATCH

Leave a Comment