query help please...

[BioMash]

I have the following tables
tblProduct
tblValue
tblProductValue

each product can have many values. I need to make a price change of 7% accross the board and am writing a script to delete each row in the productvalue table and create a new value and assign that value to the product.

I would appreciate some help as I do not write much sql and although I have read up over the last few days and thought I was on the right track I cannot get the script below to work and it just runs indefinately without making any changes to the table:

I have the following script:



declare @intProductID int
declare @intValueID int
declare @intOldValueID int
declare @intCurrencyID int
declare @intTaxID int
declare @fltTaxPercent float
declare @fltValueAmount float
declare @NewValueAmount smallmoney

DECLARE c1 CURSOR FOR

Select pv.intProductID,
v.intValueID,
v.intCurrencyID,
v.intTaxID,
v.fltTaxPercent,
v.fltValueAmount

FROM tblProductValue pv, tblValue v
WHERE pv.intValueID = v.intValueID

OPEN c1

FETCH NEXT FROM c1

INTO @intProductID, @intOldValueID, @intCurrencyID, @intTaxID, @fltTaxPercent, @fltValueAmount

WHILE @@FETCH_STATUS = 0
BEGIN

SET @NewValueAmount = CEILING(@fltValueAmount + (@fltValueAmount * 0.07))

EXEC sp_Product_DeleteProductValue @intProductID = @intProductID, @intValueID = @intoldValueID

EXEC @intValueID = sp_Value_Insert @intCurrencyID = @intCurrencyID, @intTaxID = @intTaxID, @fltTaxPercent = @fltTaxPercent, @fltValueAmount = @NewValueAmount

EXEC sp_ProductValue_Assign @intProductID = @intProductID, @intValueID = @intValueID

END

CLOSE c1
DEALLOCATE c1

 

[Juice05]

If all you need is to update your prices by 7% then this should be pretty simple. Please provide your table fields with some sample data.

 

[carp]

UPDATE tblValue
SET fltValueAmount = fltValueAmount * 1.07;
COMMIT;