Not able to get the stored procedure's result in variable in sql server? -

this question has answer here:

• get value output sql server stored procedure variable 1 answer

i have hierarchy of stored procedures calling 1 in below:

1  2   3 

now doing is: first of showing 1st level sp .

create proc proc_test3 ( @id uniqueidentifier, @value varchar(100) ) declare @outputvalue varchar(100) if @id='2' begin exec @outputvalue= proc_test2 @id @value select @outputvalue end 

here second level :

create proc proc_test2 ( @id uniqueidentifier, @value varchar(100) ) declare @outputvalue varchar(100) if @id='2' begin exec @outputvalue= proc_test1 @id @value select @outputvalue end 

and here last 3rd level :

create proc proc_test1 ( @id uniqueidentifier, @value varchar(100) ) update tblsample set value=@value id=@id select 1 

i have paste example in-actual code complex read, have implemented in simpler way every can understand easily,

now problem: every time got @outputvalue=0 when execute "proc_test3", please me can come out prob, if doing things in wrong way please let me know right way, please me resolve issue.

try use return construction -

create proc proc_test3 (     @id uniqueidentifier,     @value varchar(100) ) begin      if @id = '2' begin          declare @outputvalue varchar(100)         exec @outputvalue = dbo.proc_test2 @id, @value         return @outputvalue      end  end   create proc proc_test2 (     @id uniqueidentifier,     @value varchar(100) ) begin      if @id='2' begin          declare @outputvalue varchar(100)         exec @outputvalue= proc_test1 @id, @value         return @outputvalue      end  end   create proc proc_test1 (     @id uniqueidentifier,     @value varchar(100) ) begin      update tblsample      set value=@value      id=@id      return 1  end