Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to assign +/- to SP in MSSQL?

Posted on 2009-04-06
6
Medium Priority
?
242 Views
Last Modified: 2012-05-06
Hi
I pass a transactiontype int to a SP
If transactiontype = 1 or 2, then -
If transactiontype = 3 or 4, then +

I need a dynamic calculation.
How to change:
update clientbalance set balance = (@balance - @amount)
to
If @transactiontype = 1 or 2 set @sign = '-'
If @transactiontype = 3 or 4 set @sign = '+'
update clientbalance set balance = (@balance @sign @amount)

Open in new window

0
Comment
Question by:techques
  • 3
  • 2
6 Comments
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 90 total points
ID: 24080711
try a case to do the the if logic in line for you.
update clientbalance set balance = (@balance + case when @transactiontype in (1,2) then -@amount when @transactiontype in (3,4) then @amount end)
0
 

Author Comment

by:techques
ID: 24080788
If I have more @transactiontype condition, e.g. 1,2,5,6,8 then +

Does it just when @transactiontype in (1,2,5,6,8) then @amount end ?
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24080885
yes, you can add other @transactiontype values to either the + or - side.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 60 total points
ID: 24080966
Here you go.  Use a case statement.
update clientbalance 
set balance = 
case when @transactiontype in (1,2) then (@balance - @amount)
     when @transactiontype in (3,4) then (@balance + @amount)
end

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24080981
Sorry CG.  It didn't look like your first post covered it but it appears to.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24081780
I just structured it a little differently.  Either would work.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

571 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