Hi, can someone help me out with a query, im really stuck as to why
this doesnt work :(

Ive got a tree of data, with the tree stored using nested sets. Each
node on the tree has a "mode". mode 0 means "inherit the mode from my
parent", anything other than 0 is an explicit mode. I need to
calculate the inherited mode of each node, so that in the case that a
node has the mode "0", i can look its inherited mode and use that.

Here is how i want the data returned, "left" and "mode" are properties
of each tree node, and i need to calculated "inherited mode".

Left | Mode | Inherited Mode
10 |  0   | 1
9  |  1   |  2
8  |  0   |  2
7  |  2   |  0
6  |  0   |  0

What im doing to return the above table is:

SELECT        T1.Left, T1.Mode, dbo.fn_Mode_Inherited(T1.NodeId) AS
Mode_Inherited
FROM        tblTree T1
INNER JOIN    tblTree T2 ON    T1.Left < pT.Left
                        AND T1.Right > pT.Right
ORDER BY    T1.Left DESC

(there are where clauses to narrow me down to a specific part of the
tree, but theyre irrelevant)

inside the function is the following code:

DECLARE        @Mode_Inherited INT
SET            @Mode_Inherited = 0
SELECT        @Mode_Inherited = CASE
                WHEN @Mode_Inherited = 0 THEN T1.Mode
                ELSE @Mode_Inherited END
FROM        tblTree T1
INNER JOIN    tblTree T2 ON    T1.Left < T2.Left
                        AND T1.Right > T2.Right
                        AND T2.NodeId = NodeId
ORDER BY    T1.Left DESC
RETURN        @Mode_Inherited

The problem is that my "inherited mode" column is always returned as
0, and i have no idea why.

Any help would be greatly appreciated

Thanks!
Andrew