Solved

Stored procedure to calculate fields and insert value

Posted on 2015-01-26
2
185 Views
Last Modified: 2015-01-26
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
Comment
Question by:rtay
2 Comments
 
LVL 18

Expert Comment

by:SimonAdept
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

Open in new window

Do you want to update a single record (LoadID) at a time or the whole table?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40571511
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now