Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sproc inside another sproc (SQL Server 2008)

Posted on 2012-03-20
5
Medium Priority
?
377 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
[X]
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
5 Comments
 
LVL 11

Accepted Solution

by:
dougaug earned 1200 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 600 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 200 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

722 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