Solved

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

Posted on 2012-03-22
10
268 Views
Last Modified: 2012-03-26
Hi there,

Following another EE post of mine on this matter, I guess I am "near" the proper solution...

Could someone tell me what is wrong with the code below?

(The first two EXECs refer to sprocs, the last one refers to a function.)

The error message is (bold in the line referred):
Incorrect syntax near '@UserID'.

----------------------------------------------------------------------------
ALTER PROC [dbo].[PercentNormalizScore_sproc]
@UserID int,
@MaterialID int
As
BEGIN
  CREATE table #t(averageMockValue money, sigmaMockValue money, percNormalScore money)
  INSERT into #t(averageMockValue) EXEC AverageMock_sproc @MaterialID
  INSERT into #t(sigmaMockValue) EXEC SigmaMock_sproc @MaterialID
  INSERT into #t(percNormalScore) EXEC NormalizPercentScore(@UserID, @MaterialID)  
  UPDATE dbo.MaterialDone
     SET
      AverageMock =  #t.averageMockValue,
      SigmaMock = #t.sigmaMockValue,
      PercentNormalizScore = #t.percNormalScore
     FROM dbo.MatDone
     WHERE dbo.MaterialDone.UserID = @UserID AND dbo.MaterialDone.MaterialID = @MaterialID
 
END
---------------------------------------------------------------------------------------

Thanks,
fskilnik.
0
Comment
Question by:fskilnik
  • 5
  • 5
10 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 37755758
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!)
0
 

Author Comment

by:fskilnik
ID: 37756537
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.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 37757089
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
0
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.

 

Author Comment

by:fskilnik
ID: 37758778
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
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 37759751
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 ! ).
0
 

Author Comment

by:fskilnik
ID: 37761214
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...)
0
 
LVL 16

Accepted Solution

by:
DcpKing earned 500 total points
ID: 37762019
Last things first: I'd prefer to earn the points :)
Next, I suspect that you've not used sprocs before. You can either get a simple return value (and why you're not I can't say, as I haven't seen the inside of the sprocs), or return the value through a parameter - here's the layout of an example:
create procedure dbo.fred
(
@strInputString   varchar(32),
@strOutputValue  varchar(6) OUT
)
as
Begin
   if len(@strInputString) > 0
       set @strOutputValue = 'Filled'
   else
        set  @strOutputValue = 'Empty'
End
and you can call it like this:
declare @str1 varchar(32), @str2 varchar(6)
set @str1 = 'I am a string'
exec dbo.fred @str1, @str2 OUT
print @str2
In this case you should get "Filled" back.

In your stored procedures I think you were never returning any info. Perhaps you could show one of them here and I might be able to see a problem in it ...
0
 

Author Comment

by:fskilnik
ID: 37763740
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.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 37764369
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
0
 

Author Comment

by:fskilnik
ID: 37765172
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.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

820 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