Posted by Ed Murphy on 08/31/06 12:45

Jack Turnbull wrote:

> Hi,
> Am new to Stored Procedures and am lost how to achieve the following. I have
> this table:-
>
> CREATE TABLE [dbo].[docs] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ParentID] [int] NULL ,
> [Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Link] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> I want to write a stored procedure to return a single column table. The
> first field should contain the result of:
>
> SELECT ParentID WHERE ID = @id
>
> @id being the procedure input parameter.
>
> The procedure should then iterate through the table returning subsequent
> (single column) rows containing the result of:
>
> SELECT ParentID WHERE ID = @PreviousRowParentID
>
> Until NULL is returned
>
> Can anyone help?

Yes.
</pedant>

declare @ancestor_id int, @candidate_id int
set @ancestor_id = @id
set @candidate_id = @id
while @candidate_id is not null
begin
set @candidate_id = (
select ParentID
from table
where id = @ancestor_id
)
if @candidate_ancestor_id is not null
begin
set @ancestor_id = @candidate_id
end
end

[Back to original message]