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]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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.