asked on

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.

ASKER CERTIFIED SOLUTION

dougaug

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

LCSandman8301

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fskilnik

ASKER

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

fskilnik

ASKER

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.