rtay
asked on
Stored procedure to calculate fields and insert value
I have a question regarding creating a stored procedure to calculate a value from fields in a table using an IF statement and then insert that value to the table.
simplified example table structure all numeric... KEY, Weight, MinTon, BilledTon
Weight is in pounds and needs to be converted to tons in stored procedure
Weight / 2000
MinTon is a numeric value of tons example= 25
Billedton will be the inserted value of the calculation
I have played with creating a stored procedure but just can not get the syntax correct. Here is the non working sample
@BilledTon NUMERIC(18,0) output
)
AS
BEGIN
DECLARE @Weight numeric
DECLARE @MinTon numeric
Select @Weight = (Weight / 2000) From tbl_GenFrieght
Select @MinTon = MinTon From tbl_GenFrieght
WHERE LoadID = LoadID
Update tbl_GenFrieght
SET
BilledTon = Case When (@Weight < @BilledTon) Then @MinTon
Else @Weight
SELECT BilledTon FROM tbl_GenFrieght
End
So the value needs to Check if Weight is less than the MinTon value Then enter the MinTon Value into BilledTon
Else Enter the value of Weight / 2000 into the billedton Field.
sample data:
Weight MinTon Billedton
45000 (22,5) 25 25
51000 (25.5) 25 25.5
Thank you
simplified example table structure all numeric... KEY, Weight, MinTon, BilledTon
Weight is in pounds and needs to be converted to tons in stored procedure
Weight / 2000
MinTon is a numeric value of tons example= 25
Billedton will be the inserted value of the calculation
I have played with creating a stored procedure but just can not get the syntax correct. Here is the non working sample
@BilledTon NUMERIC(18,0) output
)
AS
BEGIN
DECLARE @Weight numeric
DECLARE @MinTon numeric
Select @Weight = (Weight / 2000) From tbl_GenFrieght
Select @MinTon = MinTon From tbl_GenFrieght
WHERE LoadID = LoadID
Update tbl_GenFrieght
SET
BilledTon = Case When (@Weight < @BilledTon) Then @MinTon
Else @Weight
SELECT BilledTon FROM tbl_GenFrieght
End
So the value needs to Check if Weight is less than the MinTon value Then enter the MinTon Value into BilledTon
Else Enter the value of Weight / 2000 into the billedton Field.
sample data:
Weight MinTon Billedton
45000 (22,5) 25 25
51000 (25.5) 25 25.5
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
Do you want to update a single record (LoadID) at a time or the whole table?