Count multilevel data

Helo to all I have a table in sql server 2005
Suppose Goldy join kshama   kshama join Harsh  Harsh join  Gaurav and Gaurav join Moni
Now i want to calculate total joinning of Goldy
according to me it should come 4 of Goldy   3 of Kshama  2 of Harsh and 1 Of Gaurav
Now what will be the query
My table is like

Now To achieve this goal I will use With Clause

with emct(id,parentid,name1,usercode,lvel) as

(

select id,parentid,name1,usercode,0  from multilevel 

where id =1(change your id from here)

union all

select main.id,main.parentid,main.name1,main.usercode,lvel +1

from multilevel as main join emct as temp on main.parentid=temp.id

)

select id,parentid,name1,usercode,lvel from emct;

go

Create stored procedure and get value return from it

Now i am going to tell You that how to make return from stored Procedure

Suppose this is my Stored Procedure

create Proc AddNewscrip


@unique as nvarchar(50),
as

if(@unique ='insert')
begin
if exists(select scripname from scripmaster where scripname=@scripname)
return 1
else
return 0
end

Now at .cs page You can retrive it like

SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(returnValue);
Now Open the Connection
conn.open();

cmd.ExecuteNonQuery();
            conn.Close();
            return Convert.ToInt32(returnValue.Value);

In this way you can get return value 0 or may be 1 depending on Your condition