sql server programming: -- For SQL Server 2005 use can use recursive CTEs, like this

CREATE TABLE MyTable(ID int, SubID int, Description varchar(30), Stat
bit)
INSERT INTO MyTable(ID,SubID,Description,Stat)
SELECT 1, 0, 'This is my test', 0 UNION ALL
SELECT 2, 1, 'this is my test to test', 0 UNION ALL
SELECT 3, 2, 'sub sub to 2-1 rec', 0 UNION ALL
SELECT 4, 2, 'dffdfd', 0 UNION ALL
SELECT 5, 0, 'completely new proj', 1

DECLARE @ID INT
SET @ID=1;

WITH CTEMyTable(ID)
AS(
SELECT ID
FROM MyTable
WHERE ID=@ID
UNION ALL
SELECT a.ID
FROM MyTable a
INNER JOIN CTEMyTable b ON b.ID=a.SubID
)
UPDATE MyTable
SET Stat=1
WHERE ID IN (SELECT ID FROM CTEMyTable)

SELECT * FROM MyTable

See if this helps:
http://milambda.blogspot.com/2005/07/climbing-trees-is-for-monkeys.html


ML

---

Hi!

I have a table with the folowing fields...

ID int,
SubID, int
Description varchar(30),
Stat bit.

the table contains

1, 0, This is my test, false
2, 1, this is my test to test, false
3, 2, sub sub to 2-1 rec, false
4, 2 dffdfd, false
5, 0, completely new proj, false

When i set the Stat field to true on the 1, 0 record, i also want it to be
set on the 2,1, 3,2, 4,2 since those are togeather in the logic but not the
5,0 And if i set it to true on the 2,1 the, 3,2, 4,2 will be set to true as
well... (Hope you understand what i want to do)

I just cant figure it out on how to do it with a recursive sproc.

Any advice

regards
Martin