Solved

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

Posted on 2012-03-22
10
261 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:fskilnik
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:fskilnik
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now