Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

sproc inside another sproc (SQL Server 2008)

Posted on 2012-03-20
5
342 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

839 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