Stored Procedure to return table?

[Jack Turnbull]

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 = @PreviousRowPar entID

Until NULL is returned

Can anyone help?

Thanks,
Jack

[Ed Murphy]

Re: Stored Procedure to return table?

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 = @PreviousRowPar entID
>
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_ance stor_id is not null
begin
set @ancestor_id = @candidate_id
end
end

[Jack Turnbull]

Re: Stored Procedure to return table?

Thanks Ed,

Put me on the right track - ended up creating a function that returned a
table using your iteration.

Thanks,
Jack

"Ed Murphy" <emurphy42@soca l.rr.comwrote in message
news:URAJg.6350 $S_5.5724@torna do.socal.rr.com ...

>
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_ance stor_id is not null
begin
set @ancestor_id = @candidate_id
end
end

[Erland Sommarskog]

Re: Stored Procedure to return table?

Jack Turnbull (turnbull.jack@ ntlworld.com) writes:

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 = @PreviousRowPar entID

;WITH rekurs (ID) AS
( SELECT ParentID FROM docs WHERE ID = @id
UNION ALL
SELECT d.ParentID
FROM docs d
JOIN rekurs r ON d.ParendID = r.ID)
SELECT ID FROM rekurs

This query requires SQL 2005.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx