CODEWITHSUNDEEP
sql-server
Patrick
Patrick

Reputation: 23

Increasing or decreasing a price by 10% within a stored procedure

I want to update a product's price by a given percentage within a stored procedure's parameter. The way, how I've written my stored procedure, it runs but does not change the price. UPDATE! When i give minus 10% it does not decrease the price. Do I have a mistake with else statement?

If the given percentage (int) is a positive int, it should increase the price but if the given percentage (int) is negative, it should decrease the price.

This is my code which does not change anything:

CREATE PROCEDURE p_rabatt 
    (@articleID int, 
     @percentage int)
AS
    IF (@percentage > 0)
    BEGIN
        UPDATE Artikel 
        SET Preis = (Preis * (1 + (@percentage / 100))) 
        WHERE ArtikelCode = @articleID
    END
    ELSE
    BEGIN
        UPDATE Artikel 
        SET Preis = (Preis - ((Preis * (1 + ((@percentage - (2 * (@percentage))) / 100))))) 
        WHERE ArtikelCode = @articleID  --mathematically it is correct
    END

This code shows error, in case you could offer such solution!

SET Preis = Preis * 1.(@percentage) 
WHERE ArtikelCode = @articleID

Please, I need a gentle feedback guys. I really need your help

Upvotes: 0

Views: 1555

Answers (1)

basodre
basodre

Reputation: 5770

I don't think you need the IF statement in you SQL code. Since you're passing in either a positive or a negative value, you can simply build the multiplier, then multiply that by the original value.

If the value is positive, we should multiple 1 + the percentage passed in. If negative, we must subtract the value passed in from 1.

UPDATE Artikel 
SET Preis = (Preis * (1 + (@percentage / 100.0))) 
WHERE ArtikelCode = @articleID

For example, if the original value of Preis is 100, and @precentage = -10, then the result is 90, which is correct.

Upvotes: 2

Related Questions