Solved

# Stored procedure to calculate fields and insert value

Posted on 2015-01-26
185 Views
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

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
Question by:rtay

LVL 18

Expert Comment

ID: 40571503
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
``````
Do you want to update a single record (LoadID) at a time or the whole table?
0

LVL 69

Accepted Solution

ScottPletcher earned 500 total points
ID: 40571511
CREATE PROCEDURE proc_name
@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

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.