Solved

How to assign +/- to SP in MSSQL?

Posted on 2009-04-06
6
223 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
[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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Independent Software Vendors: 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

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 …
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 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