Solved

How to assign +/- to SP in MSSQL?

Posted on 2009-04-06
6
185 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now