MsSql 2000 question ??

[arul]

I have a single table "amount" which has only three columns such as memberid referenceid and AMOUNT with following data ........
memberid referenceid AMOUNT
L001 L001
L004 L003
L003 L002
L002 L001
and so on
my needed query willbe if i choose L003 from referenceid the query should select the column where it was a memberid and again it choose the same row refernceid of(L003) as L002 where L002 was a memberid and choose L002s referenceid which is L001
Finally i should REACH the memberid(L001) and
the other one i need is i should return the count of no of rows crossed ,
and my biq question is everytime the query gets into new row i should add some data in amount (50,25,12.5,10,7.5,5,4,3,2,1)for (immediate row,next row,next row and so on)
how can i do this in MSSQL2000??
I have crossed accross many forums pls help im stuck up

[PABluesMan]

/* Recursive stored procedure that does the actual work */
CREATE PROCEDURE RecursiveProc

@Param VARCHAR (64) = NULL

AS
BEGIN
DECLARE @MemberID VARCHAR (64)
DECLARE @ReferenceID VARCHAR (64)
DECLARE @MinMemberID VARCHAR (64)
DECLARE @FetchStatus INT

DECLARE curAny CURSOR FOR
SELECT a.memberid, a.referenceid, min (b.memberid)
FROM amount a
CROSS JOIN (SELECT MIN (memberid) AS memberid FROM amount) b
WHERE a.memberid = ISNULL (@Param, a.memberid)
GROUP BY a.memberid, a.referenceid
OPEN curAny
FETCH NEXT FROM curAny INTO @MemberID, @ReferenceID, @MinMemberID
SET @FetchStatus = @@FETCH_STATUS
WHILE @FetchStatus = 0
BEGIN
IF @MemberID <> @MinMemberID
EXEC RecursiveProc @ReferenceID

< Logic to perform your amount and row count calculations >

FETCH NEXT FROM curAny INTO @MemberID, @ReferenceID, @MinMemberID
SET @FetchStatus = @@FETCH_STATUS
END
CLOSE curAny
DEALLOCATE curAny
END
GO
/* Procedure that initially calls the recursive proc */
CREATE PROCEDURE CallRecursiveProc

@Param VARCHAR (64) = NULL

AS
BEGIN
EXEC RecursiveProc @Param
END

[semut_hitam]

anybody here plz help me...
suggest me any appropriate link to get the best tutorial for
SQL 2000

[skhanal]

Search for "Expanding Hierarchies" in SQL 2000 books online. It has an example on how to traverse a hierarchical table with recursion.