Avoiding while loop in SQL Server

Question

I have a table that holds identifiers of objects and identifiers of their parents. It is used to hold the data objects that are presented as a tree. I want to query the table to build a full path to an object in the tree. The tree's max depth will probably never go beyond 20 objects. Is there a way to do this without a while loop...or is it worth trying to avoid a while loop for this type/size of work.

The table looks like this:

CREATE TABLE [dbo].[tblObj]
(
    [ObjectId] [int] NOT NULL,
    [ObjectName] [nvarchar](50) NOT NULL,
    [ParentId] [int] NULL,
)

with data like this

insert into tblObj (ObjectId, ObjectName) values (1, 'Root')
insert into tblObj (ObjectId, ObjectName, ParentId) values (2, 'Middle1', 1)
insert into tblObj (ObjectId, ObjectName, ParentId) values (3, 'Middle2', 2)
insert into tblObj (ObjectId, ObjectName, ParentId) values (4, 'Leaf', 3)

The desired result is to use the object/parent relationships to build a string with the object names that reflect the full path. The data above would result in the path "Root\Middle1\Middle2\Leaf"

Example of expected results? You probably need a recursive CTE anyway. — Martin Smith
– Martin Smith, Commented Aug 26, 2013 at 16:24

i-one · Accepted Answer · 2013-08-26 19:32:03Z

2

Recursive CTE is quite commonly used for this kind of tasks. Below query will give a list of (ObjectId, Path) pairs for all rows in the dbo.tblObj:

;WITH cte (ObjectId, ParentID, [ObjectName], Path)
AS(
    SELECT [ObjectId], [ParentID], [ObjectName], cast([ObjectName] as nvarchar(max))
    FROM dbo.tblObj
    WHERE [ParentID] is NULL
    UNION ALL
    SELECT t.[ObjectId], t.ParentID, t.[ObjectName], cte.Path + '\' + t.[ObjectName]
    FROM cte
        JOIN dbo.tblObj t ON t.ParentID = cte.[ObjectId]
)
select ObjectID, Path
from cte

In case if you need to obtain path of a particular object, you can do it as:

declare @objId int
set @objId = 4

;WITH cte (ObjectId, ParentID, [ObjectName], Path)
AS(
    -- here code is the same as above
)
select Path
from cte
where ObjectID = @objId

or, alternatively, as:

declare @objId int
set @objId = 4

;with PathSteps(ObjectId, ParentID, ObjectName, Level)
as
(
    select ObjectId, ParentID, ObjectName, 0
    from dbo.tblObj
    where ID = @id
    union all
    select t.ObjectId, t.ParentID, t.ObjectName, p.Level - 1
    from dbo.tblObj t
        join PathSteps p on p.ParentID = t.ID
),
Path(ObjectPath) as (
    select stuff(
        (select '\' + ObjectName
        from PathSteps
        order by Level
        for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 1, '')
)
select ObjectPath
from Path

edited Aug 26, 2013 at 19:32

answered Aug 26, 2013 at 17:42

i-one

5,1201 gold badge33 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ken Over a year ago

That does it - thanks. And thanks for providing the second portion that finds the path of a particular object. That's exactly what I have to do.

i-one Over a year ago

@Ken, I'm not sure about performance of that second portion (may be it is doing too much for just single object), so I updated answer and added alternative approach. If you run into performance issue, you may try alternative.

Nander Speerstra · Accepted Answer · 2016-09-07 08:44:26Z

0

A simple way to achieve the result is by using:

DECLARE @var VARCHAR(MAX) = ''
SELECT @var = CASE WHEN @var = '' THEN '' ELSE @var+'/' END + ObjectName 
FROM tblObj ORDER BY ObjectId
PRINT @var

edited Sep 7, 2016 at 8:44

Nander Speerstra

1,5366 gold badges26 silver badges30 bronze badges

answered Sep 7, 2016 at 7:46

Gopakumar N.Kurup

9465 silver badges20 bronze badges

Collectives™ on Stack Overflow

Avoiding while loop in SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related