sproc inside another sproc (SQL Server 2008)

Hi there,

If I have something like this:

CREATE PROC [sprocName]
@param1 int,
@param2 int
AS
BEGIN
update [certainTable]
set
[certainTableField] = EXECUTE [anothersproc](@param1),
[anotherTableField] = EXECUTE [thirdsproc](@param2)
from [certainTable]
where [certainRestrictions]
END

How should I modify the "sintax" above to make it work?

In other words, I would like to populate some fields with values obtained running other sprocs inside the main sproc... how should I change the above "code" to do that, please?

Thanks,
fskilnik.
fskilnikAsked:
Who is Participating?
 
dougaugConnect With a Mentor Commented:
Try this:

CREATE PROC [sprocName]
@param1 int,
@param2 int
AS
BEGIN
declare @returnvalue1 [datatype],
             @returnvalue2 [datatype]

select @returnvalue1 = EXECUTE [anothersproc](@param1)
select @returnvalue2 = EXECUTE [thirdsproc](@param2)

update [certainTable]
set
[certainTableField] = @returnvalue1,
[anotherTableField] = @returnvalue2
from [certainTable]
where [certainRestrictions]
END
0
 
jogosConnect With a Mentor Commented:
The other procedures must return a value, RETURN is the statement for that.
See examples at http://msdn.microsoft.com/en-us/library/ms174998.aspx
0
 
LCSandman8301Connect With a Mentor Commented:
your alternative is to dump each result set from the Stored procedures into temp tables and read the values from there.

so:
create table #t (id int, resultval varchar(20))

insert into #t
exec sproc1
0
 
fskilnikAuthor Commented:
Hi there!

Sorry for the delay and thanks A LOT for the great suggestions/insights!

@dougaug : your structure worked nice, but the 'execute' section (see below);

@jogos : your excellent link showed me how to manage the 'execute' section, particularly on the extract:

------------------------------------
DECLARE @return_status int;
EXEC @return_status = checkstate '291';
SELECT 'Return Status' = @return_status;
GO
-------------------------------------

@LCSandman8301 : very nice alternative approach. I´ll try it in another occasion!


Just for the sake of closing my post, I´ll leave below my final (real) code, where your expertise was essential!

Regards and thanks A LOT!
fskilnik.

------------------------------------------------------------------------------
ALTER PROC [dbo].[PercentNormalizScore_sproc]
@UserID int,
@MaterialID int
As
BEGIN
    declare
    @AverageMockValue money,
    @SigmaMockValue money,
    @PercNormalScore money
   
     
    EXEC @AverageMockValue = AverageMock_sproc @MaterialID ;
    EXEC @SigmaMockValue = SigmaMock_sproc @MaterialID ;
     
    update dbo.MaterialDone
    set
    AverageMock =  @AverageMockValue,
    SigmaMock = @SigmaMockValue,
    PercentNormalizScore = dbo.NormalizPercentScore (@UserID, @MaterialID)
    From dbo.MaterialDone
    where dbo.MaterialDone.UserID = @UserID AND dbo.MaterialDone.MaterialID = @MaterialID
END
0
 
fskilnikAuthor Commented:
I realized there is something wrong with my final version: EXECs are not working...

I will open another EE post on this matter and I would be glad if you could help me on that.

Thanks,
fskilnik.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.