Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

This question already has an answer here:

i have a Hierarchy of stored procedures calling one in another as below:

1
 2
  3

Now what i am doing is: first of all i am showing the 1st level sp .

Create proc proc_test3
(
@Id uniqueidentifier,
@value varchar(100)
)
as
declare @Outputvalue varchar(100)
if @Id='2'
begin
exec @Outputvalue= proc_test2 @Id @value
select @Outputvalue
end

Here is the second level :

Create proc proc_test2
(
@Id uniqueidentifier,
@value varchar(100)
)
as
declare @Outputvalue varchar(100)
if @Id='2'
begin
exec @Outputvalue= proc_test1 @Id @value
select @Outputvalue
end

and here is last 3rd level :

Create proc proc_test1
(
@Id uniqueidentifier,
@value varchar(100)
)
as
update tblsample set value=@value where id=@Id
select 1

I have paste just an example but in-actual my code is much complex to read, so i have implemented it in some simpler way so that every can understand it easily,

Now what is my problem: every time i got @Outputvalue=0 when i execute the "proc_test3", please help me so that i can come out from this prob, if i am doing the things in some wrong way please let me know the right way, please help me resolve the issue.

share|improve this question
@GSerg hmm ok but how to overcome that can you please explain that answer to me or how can i solve the issue now,..... please guide me – raman Apr 25 at 10:46

marked as duplicate by GSerg, Nate, Mark Bell, akond, Yuushi Apr 25 at 15:46

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer

up vote 0 down vote accepted

Try to use RETURN construction -

CREATE PROC proc_test3
(
    @Id UNIQUEIDENTIFIER,
    @value VARCHAR(100)
)
AS 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)
)
AS 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)
)
AS BEGIN

    UPDATE tblsample 
    SET value=@value 
    WHERE id=@Id

    RETURN 1

END
share|improve this answer
@Raman, if you could undelete this questions: stackoverflow.com/questions/16211786/…, I would try to help you. – Devart Apr 25 at 10:51
ok.. its being asked by my junior developer.. i dont know if he elaborated it well. by the way i am asking him to undeleted the questiopn – raman Apr 25 at 10:55
thanks alot......... – raman Apr 25 at 11:02
You're welcome @raman. – Devart Apr 25 at 11:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.