Solved

How to assign +/- to SP in MSSQL?

Posted on 2009-04-06
6
229 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 27

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 27

Expert Comment

by:Chris Luttrell
ID: 24080885
yes, you can add other @transactiontype values to either the + or - side.
0
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 
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 27

Expert Comment

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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

691 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