|
|
Posted by Erland Sommarskog on 08/31/06 21:46
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 = @PreviousRowParentID
;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@sommarskog.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
Navigation:
[Reply to this message]
|