Solved

Oracle 'CONNECT BY PRIOR' Equivalent in MS-SQL

Posted on 2001-08-01
7
6,795 Views
Last Modified: 2009-05-28
Hi,
I've been working with Oracle for quite sometime now. But, off late I've started working with SQL Server. I would like to get the equivalent command/SQL statement for the 'CONNECT BY PRIOR' functionality of Oracle, in SQL Server. I've been searching for sometime & am unable to find the same. I need this urgently.

Thanks,
Deepak Ganesan
0
Comment
Question by:deepakg
  • 4
  • 2
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6343428
There is no such thing in SQL Server :-(
thus the only thing you can do is implement it using some temporary tables and a stored proc...

Cheers
0
 
LVL 2

Author Comment

by:deepakg
ID: 6343453
angel,
I know that there is an equivalent is SQL Server for 'CONNECT BY PRIOR' functionality of Oracle..
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6343525
Just to put things clear:
you are asking for the functionality CONNECT BY PRIOR, telling me that you know that it exists. So you are only asking for it's name??
I have been working some years with SQL Server and with Oracle, and i have not seen the CONNECT BY PRIOR in SQL Server (including SQL2000).
I know that ADO (used in VB/C++) can use the Shaping Provider to build recordsets that are parent-child, but still not the same as Oracle's CONNECT BY PRIOR...

I would like to hear the SQL Server's name for this functionality, but as far as i know, it's only Oracle that supplies this (strong) extention to the ANSI standards...

Cheers
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6347522
0
 
LVL 2

Author Comment

by:deepakg
ID: 6371166
Hi angelll,
Do you have any sample code for reproducing the CONNECT BY Functionality in SQL Server as you've been saying the same can be accomplished by using some temp. tables & stored procedure..

thanks,
Deepak.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 6372290
Find below the code of a stored proc that finds the hierarchy data in some tables...
Sorry if it isn't too explicit, but i coulnd't comment it better for the moment...

Hope this helps

CREATE PROCEDURE
     tsBuildHierarchy
     @root           VARCHAR ( 100 ),
     @owner     VARCHAR ( 30 ),
--      This flag indicates if the resulting data is immediately returned, or if the calling
--     procedure will use the contents of the global table ##tmpFolders itself
     @return          int      = 1              
AS

IF      @return = 1
     DROP TABLE ##tmpFolders

IF      @return <> 3
BEGIN
     IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE name = '##tmpFolders'  )
          DROP TABLE ##tmpFolders

     CREATE TABLE ##tmpFolders
          (
          oID int , pID int, oLevel int, oName varchar(255), oType int ,
          ROW int IDENTITY (1,1)
          )
END    

--      maybe we didn't be able to DROP/CREATE the table, we will then try to empty the table
TRUNCATE TABLE ##tmpFolders


DECLARE     --     store the id's of the owner and the root folder
     @oID     int,
     @rID     int

DECLARE     --      control variables
     @level          int,
     @lastcount     int

--      Translate @root and @owner to @rID and @oID
IF ( ISNUMERIC ( @owner )  =  1 )
     SET @oID = CAST ( @owner AS INT )
ELSE
     SELECT
          @oID =      o.[user_id]
     FROM
          amdb..benutzer     o          
     WHERE
          o.displayname = @owner

IF (ISNUMERIC ( @root ) = 1)
     SET @rID = CAST ( @root AS INT )
ELSE
     SELECT
          @rID = o.obj_id
     FROM
          celldb..objekt     o
     WHERE
          o.[name] = @root
     AND     o.bearbeiter = @oID

--     The root folder has 0 as level, the others will have 1 to n
SET     @level = 0

INSERT INTO
     ##tmpFolders
SELECT
     obj_id , NULL, @level, [name], objkl_id
FROM
     celldb..objekt
WHERE      1=1
AND     obj_id = @rId
AND     bearbeiter = @oId


--     Check if any (normally 0 or 1) rows have been inserted
SET     @lastcount = @@rowcount


WHILE @lastcount <> 0
--     as long as rows are inserted, we must proceed
BEGIN
     --      proceed next level
     SET @level = @level + 1    

     --      insert the folders/items on the next level
     INSERT INTO
          ##tmpFolders
     SELECT
          o.obj_id , p.oID,  @level, o.[name], o.objkl_id
     FROM
          ##tmpFolders     p
     JOIN
          celldb..objsch     r
     ON    
          r.vater_obj_id = p.oID
     AND     p.olevel = @level - 1
     JOIN    
          celldb..objekt     o
     ON    
          o.obj_id = r.sohn_obj_id
     
     --      determine how many rows have been entered this time
     SET @lastcount = @@rowcount
END


--     Check if we have to return the table
IF @return = 1 or @return = 2
BEGIN
     CREATE INDEX IDXl ON ##tmpFolders ( oLevel )
     
     --     We return the entire table, ordered by level
     SELECT
          *
     FROM
          ##tmpFolders
     ORDER BY
          oLevel        
END

0
 

Expert Comment

by:CleanupPing
ID: 9281917
deepakg:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now