?
Solved

Stored procedure to calculate fields and insert value

Posted on 2015-01-26
2
Medium Priority
?
192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 18

Expert Comment

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

770 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