Perhaps I have a Logic Error? 1

[vonehle]

As is, this code executes with no errors; however, it doesn't seem to be performing the calculation. I think this may be because the line SET @MajorID = MajorID is in the wrong place. Anytime I try to move it though, I get an error Msg 102, Level 15 State 1 Line 2. For the life of me, I can't figure out why line 2 would suddenly be a problem just because the SET was moved.

DROP TABLE BookPriceIncrease_ByMajor

SELECT Majors.MajorID, Majors.Description, Books.BookID,
Books.Title, Books.AuthorName, Books.pub_id,
Books.BooksOnHand, Books.Price 
INTO BookPriceIncrease_ByMajor

FROM Majors, Books  

GROUP BY Majors.MajorID, Majors.Description, Books.BookID,
Books.Title, Books.AuthorName, Books.pub_id, 
Books.BooksOnHand, Books.Price;

GO
DROP PROCEDURE sp_PriceIncrease 
Go

CREATE PROCEDURE sp_PriceIncrease(@MajorID int , @Percent int = 5)
AS UPDATE BookPriceIncrease_ByMajor
SET Price = Price + (Price*(@Percent/100))
     
   UPDATE BookPriceIncrease_ByMajor
   SET @MajorID = MajorID

GO
EXECUTE sp_PriceIncrease 1,10
GO

SELECT MajorID, BookID, Price FROM BookPriceIncrease_ByMajor;
GO

 

[cfStarlight]

Hi vonehle,

The syntax of your update statement looks wrong. I think you want something more like this..?

UPDATE BookPriceIncrease_ByMajor
SET Price = Price + (Price*(@Percent/100))
WHERE MajorID = @MajorID

 

[bborissov]

Why you want to do with

UPDATE BookPriceIncrease_ByMajor
       SET @MajorID = MajorID

Update the field? or
Set the variable value

If you want to Update the field the correct syntax is:

UPDATE BookPriceIncrease_ByMajor
      SET MajorID = @MajorID

If you want to SET the variable value, syntax is:

SELECT @MajorID = MajorID FROM BookPriceIncrease_ByMajor

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

Keep in mind that you must have WHERE clause in both cases, I am sure you don't want to update AL the records with @MajorID value, and in second case you must have one record returned.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[vonehle]

bborissov, cfstarlight,

I've tried your suggestion, but the calculations are still not being done. I do not get any errors during execution, but none of the prices ever get changed, no matter how many times I execute it. I'm wondering if the calculation is even taking place.

 

[GHolden]

Can you repost the code that you now have.

There are two ways to write error-free programs; only the third one works.

 

[vonehle]

I like that little joke, GHolden. Do I have your permission to use that too?

DROP TABLE BookPriceIncrease_ByMajor

SELECT Majors.MajorID, Majors.Description, Books.BookID,
Books.Title, Books.AuthorName, Books.pub_id,
Books.BooksOnHand, Books.Price 
INTO BookPriceIncrease_ByMajor

FROM Majors, Books  

GROUP BY Majors.MajorID, Majors.Description, Books.BookID,
Books.Title, Books.AuthorName, Books.pub_id, 
Books.BooksOnHand, Books.Price;

GO
DROP PROCEDURE sp_PriceIncrease 
Go

CREATE PROCEDURE sp_PriceIncrease(@MajorID int , @Percent int = 5)
AS UPDATE BookPriceIncrease_ByMajor
SET Price = Price + (Price*(@Percent/100))
WHERE MajorID = @MajorID

GO
EXECUTE sp_PriceIncrease 1,15

GO

SELECT MajorID, BookID, Price FROM BookPriceIncrease_ByMajor;
GO

 

[GHolden]

Of course, feel free

If you change your UPDATE to a SELECT

i.e.

SELECT MajorID, (Price + (Price*(@Percent/100)))
FROM BookPriceIncrease_ByMajor
WHERE MajorID = @MajorID

Do you get the expected results returned?

There are two ways to write error-free programs; only the third one works.

 

[vonehle]

By adding yours directly beneath the CREATE PROCEDURE block, I get the correct @MajorID returned, but the calculation still doens't happen.

 

[GHolden]

Okay. What value is in Price before you run the calculation?



There are two ways to write error-free programs; only the third one works.

 

[GHolden]

Just to check change the code to this...

SELECT MajorID, Price, (Price + (Price*(@Percent/100)))
FROM BookPriceIncrease_ByMajor
WHERE MajorID = @MajorID

Then you should see both values of Price

There are two ways to write error-free programs; only the third one works.

 

[vonehle]

The first three are $39.95, 35.95, 37.50

After executing, table Books and table BookPriceIncrease_ByMajor still have the same values they had before executing.

 

[vonehle]

Just to check change the code to this...

SELECT MajorID, Price, (Price + (Price*(@Percent/100)))
FROM BookPriceIncrease_ByMajor
WHERE MajorID = @MajorID

Then you should see both values of Price
There are two ways to write error-free programs; only the third one works.

Tried it. Both price columns have the same value.

 

[GHolden]

What data type is the Price field?



There are two ways to write error-free programs; only the third one works.

 

[vonehle]

Price field is MONEY. I just tried changing it to FLOAT, but it didn't help.

 

[GHolden]

Change the formula to:

Price + ((Price * @Percent)/100)

There are two ways to write error-free programs; only the third one works.

 

[GHolden]

Or change @Percent to be a decimal

There are two ways to write error-free programs; only the third one works.

 

[vonehle]

You're the man, GHolden. Just a silly misplaced parenthesis. Thanks for your help!

 

[GHolden]

It's because when SQL divided a Decimal by an Int it converts the answer to an Int by default.

Therefore @Percent/100 will always be Zero (once converted to an Int)

Price * Zero will always be Zero

So Price + Zero is always Price



There are two ways to write error-free programs; only the third one works.