CODEWITHSUNDEEP
sqlsql-serverwhile-loop
Ken
Ken

Reputation: 447

Avoiding while loop in SQL Server

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"

Upvotes: 2

Views: 2438

Answers (2)

Gopakumar N.Kurup
Gopakumar N.Kurup

Reputation: 936

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

Upvotes: 0

i-one
i-one

Reputation: 5120

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

Upvotes: 2

Related Questions