Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

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.
0
fskilnik
Asked:
fskilnik
3 Solutions
 
dougaugCommented:
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
 
jogosCommented:
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
 
LCSandman8301Commented:
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now