x
Solved

# Stored procedure to calculate fields and insert value

Posted on 2015-01-26
Medium Priority
199 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 70

Accepted Solution

Scott Pletcher earned 2000 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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Join & Write a Comment Already a member? Login.

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
###### Suggested Courses
Course of the Month10 days, 23 hours left to enroll

#### 608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.