Improve company productivity with a Business Account.Sign Up

x
?
Solved

Stored procedure to calculate fields and insert value

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

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

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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

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…

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.

Join & Ask a Question