sproc inside a sproc (SQL Server 2008) - part 2

Firstly, change
      INSERT into #t(percNormalScore) EXEC NormalizPercentScore(@UserID, @MaterialID)
to this
      INSERT into #t(percNormalScore)
          SELECT NormalizPercentScore(@UserID, @MaterialID)

However, I think you'll be getting three records in #t, with one field filled in each. If so then this should help.

declare @m1 money, @m2 money, @m3 money
EXEC @m1 = AverageMock_sproc @MaterialID
EXEC @m2 = EXEC SigmaMock_sproc @MaterialID
select @m3 = NormalizPercentScore(@UserID, @MaterialID)
      INSERT into #t(percNormalScore)
          @m1, @m2, @m3

All this, of course, assumes that you're getting the info for #t as a return value from the two procedures.
Alternatively, you can return data from a stored procedure in an "OUT" parameter. If you do that, you might end up with this:

declare @m1 money, @m2 money, @m3 money
EXEC AverageMock_sproc @MaterialID, @m1
EXEC EXEC SigmaMock_sproc @MaterialID, @m2
SELECT @m3 = NormalizPercentScore(@UserID, @MaterialID)
      INSERT into #t(percNormalScore)
          @m1, @m2, @m3

Good luck!
(any typos are my fault, as I don't have a copy of SQL Server here to check things out on!)

Hi, DcpKing!

Excellent post, really helpful to show me how things should be "mixed together"...

I still could not make it work, but I guess now I am pretty near.

Could you please tell me what is wrong with the sproc I created from your suggestions (below)?

----------------------
ALTER PROC [dbo].[PercentNormalizScore_sproc]
@UserID int,
@MaterialID int
As
BEGIN
  DECLARE @AverageMock money, @SigmaMock money, @PercentNormalScore money
  EXEC @AverageMock = AverageMock_sproc @MaterialID
  EXEC @SigmaMock = SigmaMock_sproc @MaterialID
  SELECT @PercentNormalScore = dbo.NormalizPercentScore(@UserID,@MaterialID)
 
  CREATE table #t(averageMock money, sigmaMock money, percNormScore money)
  INSERT into #t(averageMock, sigmaMock, percNormScore)
  VALUES (@AverageMock, @SigmaMock, @PercentNormalScore)
 
  UPDATE dbo.MaterialDone
     SET
      AverageMock = #t.averageMock,
      SigmaMock = #t.sigmaMock,
      PercentNormalizScore = #t.percNormScore
     FROM dbo.MaterialDone
     WHERE dbo.MaterialDone.UserID = @UserID AND dbo.MaterialDone.MaterialID = @MaterialID  
END
----------------------

The error message was:

---------------------------------------
Msg 4104, Level 16, State 1, Procedure PercentNormalizScore_sproc, Line 17
The multi-part identifier "#t.averageMock" could not be bound.
Msg 4104, Level 16, State 1, Procedure PercentNormalizScore_sproc, Line 18
The multi-part identifier "#t.sigmaMock" could not be bound.
Msg 4104, Level 16, State 1, Procedure PercentNormalizScore_sproc, Line 19
The multi-part identifier "#t.percNormScore" could not be bound.
-----------------------------------------

Thanks A LOT!

Regards,
fskilnik.

I realise now (in the cold light of day) that you're only putting one record into the temp table, so you really don't need it at all!.

After your select, delete everything down to the End statement (don't kill that !)  and replace it all with this:

update dbo.MaterialDone
    set AverageMock = @AverageMock,
          SigmaMock = @SigmaMock
          PercentNormalizScore = @PercentNormalScore
    where UserID = @UserID
        and MaterialID = @MaterialID

Hi, DcpKing!

Your suggestion is exactly what I did in my first post (... - "part 1") and the function NormalizPercentScore executes and updates in the proper MatDone field, but the store procedures (averageMock_sproc and sigmaMock_sproc) do not: I don´t know if they do not execute or if the update should be different...

The code I used is as follows (below); please help me to correct it!

Thanks,
fskilnik


ALTER PROC [dbo].[PercentNormalizScore_sproc]
@UserID int,
@MaterialID int
As
BEGIN
  DECLARE @AverageMock money, @SigmaMock money, @PercentNormalScore money
  EXEC @AverageMock = AverageMock_sproc @MaterialID
  EXEC @SigmaMock = SigmaMock_sproc @MaterialID
  SELECT @PercentNormalScore = dbo.NormalizPercentScore(@UserID,@MaterialID)

  update dbo.MaterialDone
    set AverageMock = @AverageMock,
          SigmaMock = @SigmaMock,
          PercentNormalizScore = @PercentNormalScore
    where UserID = @UserID and MaterialID = @MaterialID
 END

Hi,

Please excuse me for being ham-fisted with cutting and pasting ! You're adding a new row into the table so it should be
      insert into  dbo.MaterialDone
not
      update dbo.MaterialDone

Please accept my apologies. I can only plead a mixture of stupidity and exhaustion (I'm on my last week of a contract and insanely busy ! ).

Hi!

No apologies needed, DcpKing, I am thankful you take your time and expertise to help me!

The fact is that, from the very beginning, my intent is really to UPDATE a specific row of a certain table, not to insert a new line (row) to it...

In other words, could you have a close look at my final store procedure (above) and tell me why the function is getting its value and updating that table while the store procedures are updating the corresponding fields with zeros (and that should not be the case)?

It is important to mention that those stored procedures, when run by themselves alone (not inside this store procedure) are updating the table perfectly... (I know I could call them first and then this new store procedure, just with the function to update the table, but I want to learn why my code is not working properly...)

Thanks A LOT and good luck with your contract/project!
fskilnik.

P.S.: if you would like so, I could close this EE question and give you the points for the time you have already spent here. (I could reopen as another question, no problem, for other people to have a look...)

Thanks for your detailed answer, DcpKing!

Ok! You have already certainly earned the points before your last post, but now you really SOLVED my problem, with your observations, as explained below!!

Let me start showing  you a "typical" sproc I have been using for a long time

------------------------------
ALTER PROC [dbo].[AverageMock_sproc]
@MaterialID int
As
BEGIN
    update dbo.MaterialDone
    set AverageMock = (select AVG(TotalPoints) from dbo.vw_MockUserPoints where MaterialID = @MaterialID)
    where MaterialID = @MaterialID
END
-------------------------------

As you see, I do not use the RETURN feature, nor the OUT one, and it works as I would like it, anyway...

You see? I am really not used to the sproc features you´ve mentioned, but I guess I have a really good feel for some utilities sprocs are able to do for me even so...

BUT, looking at this sproc, I realize it does not really gives a RETURN value for the sproc I´ve started asking about, therefore I cannot update the table with the new sproc, of course! The "AverageMock_sproc", as you may have observed above, updates the MaterialDone table WITHOUT returning anything... that´s it!

Now I know how to approach my new sproc: I have to RETURN a value from an almost identical "AverabeMock2_sproc", this new one (to be created) WILL return the "AVG(TotalPoints)" I want and, with this value returned, I will update my table... CHEERS!

Thanks a lot, DcpKing, you are really great!

All the best and I really hope to have your assistance in other programming issues!
Fábio.

Hi Fábio

We're all just here to help!!

Anyhow, I'd recommend that you explore the idea of using the OUT parameters, because what you can return back with the RETURN statement is really limited to just one value (you can have multiple OUT parameters.

You might find this page an interesting summary:
http://www.sqlteam.com/article/stored-procedures-returning-data

Have fun!!

Mike

Excellent, Mike!  I will study the link you provided, for sure!  :)

Thanks for this "lessons". Your posts were REALLY extremely useful and instructive.

All the best and success with your project/contract!
Fábio.