More efficient/cleaner way of doing a select that traverses a specific column? Given the following Table structure:

Id  OldUrl  Target
1    /a      /b
2    /b      /c
3    /c      /d
4    /x      /y


I want to do a select that "follows" the Target Column, example:

If I pass in ID 1 I want to return row 3 because A would resolve to C if you Select the Target and then Selects the next row where OldUrl = Target
Like this:

a -&gt; b
b -&gt; c
c -&gt; d


SQL for creating the table:

CREATE TABLE [dbo].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [OldUrl] [nvarchar](450) NOT NULL,
    [Target] [nvarchar](max) NOT NULL,
    [Wildcard] [bit] NOT NULL,
    [Disabled] [bit] NOT NULL,
    [Permanent] [bit] NOT NULL,
 CONSTRAINT [PK_dbo.MyTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


I've managed to do that by using the code below, but my SQL skills are very limited so I just want to know if there is a better/cleaner way of doing this?

DECLARE
@Id INT = 1,
@Target nvarchar(MAX) = '',
@Result nvarchar(MAX) = ''

WHILE (1=1)
    BEGIN
        SELECT @Result=(SELECT [Target] FROM MyDatabase.MyTable WHERE Id = @Id)
        if(@Result = @Target)
            break
        SELECT @Id = [Id] FROM MyDatabase.MyTable WHERE OldUrl = @Result

        SET @Target = @Result
    END

SELECT * FROM MyDatabase.MyTable WHERE Id = @Id


Wanted output when passing in Id = 1

3   /c  /d
A recursive CTE will find that for you.  By adding in the desc and TOP 1 it will look for the longest possible path.  If there were two paths it could follow it will pick one based on the ORDER statement

;WITH follow(idstart, idend, oldurl, target, depth, path) as 
(
    SELECT S.id idstart, S.id idend, S.oldurl, S.target, 1 AS depth, cast(S.oldurl as varchar) AS path
    FROM mytable as S
    UNION ALL
    SELECT S2.id idstart, p.idend idend, S2.oldurl, p.target, p.depth + 1 AS depth, cast(RTRIM(p.path) + '-&gt;' + S2.oldurl as varchar) AS path
    FROM follow AS p JOIN mytable as S2 on S2.target = p.oldurl
    WHERE p.target is not null
)
SELECT TOP 1 * FROM follow
WHERE idstart=1
ORDER BY depth desc;