Stored Procedure to return table?

Giganews Newsgroups
Subject: Stored Procedure to return table?
Posted by:  Jack Turnbull (turnbull.ja…@ntlworld.com)
Date: Thu, 31 Aug 2006

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?

Thanks,
Jack

Replies