Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Stored procedure to calculate fields and insert value

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

650 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