CODEWITHSUNDEEP
sqlloopswhile-loopsql-server-2008-r2
smartdan
smartdan

Reputation: 83

Is there a way to avoid this loop in SQL Server 2008 R2?

I've often seen that expert users advise to try avoid loops at database level (Reference here). I have a short block of code where i can't see other way to achieve the task without the use of a loop. The task is very simple but is there a way to avoid the loop?

DECLARE @id INT = 1
DECLARE @auxId INT

WHILE @id IS NOT NULL
BEGIN
    SET @auxId = @id
    SELECT @id = id_next_version FROM task WHERE id_task = @id
END

SELECT @aux

Explanation of the code:

I have a table where there are tasks and some rows are updates of other tasks, so I have a column where are the id of the next version. What I want is to find the id of the last version of a task.

EDIT:

Table structure

CREATE TABLE task
(
    id_task INT IDENTITY(1,1) NOT NULL,
    task NVARCHAR(50) NULL,
    id_next_version INT NULL
)

Upvotes: 2

Views: 579

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

You are traversing a graph -- probably a tree structure actually. You can do this with a recursive CTE:

with cte as (
    select id_task, id_next_version, 1 as lev
    from task
    where id_task = @id
    union all
    select t.id_task, t.id_next_version, cte.lev + 1
    from task t join
         cte
         on t.id_task = cte.id_next_version
   )
select top 1 *
from cte
order by lev desc;

I'm not sure that this is more elegant than your loop. It should be an iota faster because you are only passing in one query.

Here is a SQL Fiddle illustrating the code.

Upvotes: 4

Related Questions