Thursday, November 24, 2011

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.parentid,main.name1,main.usercode,lvel +1
from multilevel as main join emct as temp on

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

Wednesday, November 23, 2011

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),

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

Now at .cs page You can retrive it like

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

            return Convert.ToInt32(returnValue.Value);

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