Solved

How to assign +/- to SP in MSSQL?

Posted on 2009-04-06
6
215 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 26

Accepted Solution

by:
Chris Luttrell earned 30 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 26

Expert Comment

by:Chris Luttrell
ID: 24080885
yes, you can add other @transactiontype values to either the + or - side.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 20 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 26

Expert Comment

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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 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