12,888,170 members (47,319 online)
Rate this:
See more:
Given below is my code in ms sql2008 here my empty node contain the value null ,,,
please help me out from this ...or give any other program for counting the node in mssql 2008 for binary tree...

```ALTER FUNCTION [vainimarketing].[Countnode](@node AS varchar(50))
Returns INT
AS
BEGIN
Declare @lnode Varchar(50)
Declare @rnode Varchar(50)
Declare @result INT
Select @lnode=LeftChild,@rnode=RightChild From Tree where ParentId=@node;
if(@node='null')
set @result=0;
if(@lnode='null' AND @rnode='null')
set @result=1;
else
Set @result=vainimarketing.Countnode(@lnode)+vainimarketing.Countnode(@rnode);
return @result;
END```
Posted 18-Dec-12 21:29pm

Rate this:

## Solution 1

Try ::
```WITH CTE_Node(
NodeID,
NodeRigth,
NodeLeft,
Level,
RigthOrLeft
)
AS
(
SELECT
NodeID,
NodeRigth,
NodeLeft,
0 AS Level,
'P'
FROM Node
WHERE NodeID = 1

UNION ALL

SELECT
Node.NodeID,
Node.NodeRigth,
Node.NodeLeft,
Level + 1,
CASE WHEN CTE_Node.NodeLeft = Node.NodeID THEN 'R' ELSE 'L' END
FROM Node
INNER JOIN CTE_Node ON CTE_Node.NodeLeft = Node.NodeID
OR CTE_Node.NodeRigth = Node.NodeID
)
SELECT DISTINCT RigthOrLeft,
COUNT(NodeID) OVER(PARTITION BY RigthOrLeft)
FROM CTE_Node```
v3
rajan1189 19-Dec-12 4:15am

hello Kuthuparakkal ....
my tree is containg three column parentid,leftchild,rightchild,if any of the child is empty ,it is set to 'null' which is a string or varchar...
Kuthuparakkal 19-Dec-12 4:21am

Okay posted new solution, Modify to match to your table name, column name etc..
rajan1189 19-Dec-12 6:02am

@Kuthuparakkal sir i never use this cte,i need to calculate tree leaves,In my table three column are there Parentid,leftchildid,rightchildid and i am using sql server 2008,and the given function is a scalar function,from this i m fetching the value in stored procedure,so please help me out if we can do it by function only ,,if not then please do chage it according ,,i need value return from function too..thanks in advance,,
rajan1189 19-Dec-12 6:02am

@Kuthuparakkal sir i never use this cte,i need to calculate tree leaves,In my table three column are there Parentid,leftchildid,rightchildid and i am using sql server 2008,and the given function is a scalar function,from this i m fetching the value in stored procedure,so please help me out if we can do it by function only ,,if not then please do chage it according ,,i need value return from function too..thanks in advance,,
rajan1189 19-Dec-12 6:03am

please do change in the existing function ..

Top Experts
Last 24hrsThis month
 CHill60 515 Jochen Arndt 285 OriginalGriff 260 Dave Kreskowiak 160 F-ES Sitecore 125
 OriginalGriff 4,472 CHill60 3,028 Karthik Bangalore 2,436 Jochen Arndt 2,428 ppolymorphe 1,995