Solved

sproc inside another sproc (SQL Server 2008)

Posted on 2012-03-20
5
339 Views
Last Modified: 2012-03-22
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
Comment
Question by:fskilnik
5 Comments
 
LVL 11

Accepted Solution

by:
dougaug earned 300 total points
ID: 37745630
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
 
LVL 25

Assisted Solution

by:jogos
jogos earned 150 total points
ID: 37745632
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
 
LVL 6

Assisted Solution

by:LCSandman8301
LCSandman8301 earned 50 total points
ID: 37747360
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
 

Author Comment

by:fskilnik
ID: 37751896
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
 

Author Comment

by:fskilnik
ID: 37754969
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question