CTE recursive ,Binary tree node count..

See more:

C#

ASP.NET

database

ALTER PROCEDURE [vainimarketing].[ppairincome]
@key varchar(50)
AS
BEGIN
DECLARE @parent Varchar(50)
DECLARE @lcount INT
DECLARE @pair INT
DECLARE @rcount INT
	SET NOCOUNT ON;
While(@key!='VA251000')
Begin
Select @parent=ParentId from Tree Where ChildId=@key;
Select @pair=TotalPair From Tree where ChildId=@parent; 

with cte as (
       select
               ChildId, ParentId, position,
               null lnode,
               null rnode
       from Tree where ChildId = @parent
       union all
       select
               t.ChildId, t.ParentId, t.position,
               ISNULL(cte.lnode, CASE WHEN t.position = 'LEFT' THEN 1 ELSE 0 END) lnode,
               ISNULL(cte.rnode, CASE WHEN t.position = 'RIGHT' THEN 1 ELSE 0 END) rnode
       from Tree t
       inner join cte
               on cte.ChildId = t.ParentId
)
select SUM(lnode)LeftNodes,SUM(rnode)RightNodes from cte;
set @key=@parent;
End
END

In this I would like to store the value of SUM(lnode)LeftNodes and SUM(rnode)RightNodes in @lcount and @rcount respectively.

Please help it is urgent ...

I need to do something like

select @lcount=SUM(lnode)LeftNodes,@rcount=SUM(rnode)RightNodes from cte;