Oracle 'CONNECT BY PRIOR' Equivalent in MS-SQL

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
LVL 2
deepakgAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
deepakgAuthor Commented:
angel,
I know that there is an equivalent is SQL Server for 'CONNECT BY PRIOR' functionality of Oracle..
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
deepakgAuthor Commented:
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
 
CleanupPingCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.