• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7291
  • Last Modified:

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
0
deepakg
Asked:
deepakg
  • 4
  • 2
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now