Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

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
0
rtay
Asked:
rtay
1 Solution
 
SimonCommented:
I think you just need an UPDATE statement.
update @tbl_Genfrieght 
set BilledTon=case when cast(weight as decimal)/2000 <minton then minton else cast(weight as decimal)/2000 end

Open in new window

Do you want to update a single record (LoadID) at a time or the whole table?
0
 
Scott PletcherSenior DBACommented:
CREATE PROCEDURE proc_name
 @LoadID int,
 @BilledTon NUMERIC(18, 2) = NULL output
AS
 
SET NOCOUNT ON;

DECLARE @output TABLE (
    Weight numeric(18, 2),
    MinTon numeric(18, 2)
    )

UPDATE gf
SET BilledTon = CASE WHEN (gf.Weight / 2000.0) < gf.MinTon THEN gf.MinTon ELSE (gf.Weight / 2000) END
OUTPUT INSERTED.Weight, INSERTED.MinTon INTO @output
FROM tbl_GenFrieght gf
WHERE gf.LoadID = @LoadID

SELECT @BilledTon = CASE WHEN (o.Weight / 2000.0) < o.MinTon THEN o.MinTon ELSE (o.Weight / 2000) END
FROM @output o

GO --end of proc
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now