want to ensure > 0 when subtracting from int field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

want to ensure > 0 when subtracting from int field

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-10-11 : 11:11:41

Hey,

I'm doing a subtraction on a field eg.

SET Salary = Salary - @fine

I want to make sure that salary does not go less than 0, whats the best approach here?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-11 : 11:20:48

SET Salary = case when Salary < @fine then salary else Salary - @fine end

if ths't not ok i guess trigger is the way to go...
it depends on what you want to do when the diff is less than 0.

Go with the flow & have fun! Else fight the flow

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-10-11 : 11:25:36

Or this....
[CODE]
declare @Salary decimal(18, 2)
declare @fine decimal(18, 2)

set @salary = 100
set @fine = 100.05

select @salary = case when abs(@salary - @fine) = (@salary - @fine) then @salary - @fine else @salary end
select @salary

set @salary = 1000
set @fine = 100.05

select @salary = case when abs(@salary - @fine) = (@salary - @fine) then @salary - @fine else @salary end
select @salary
[/CODE]

Duane.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-11 : 11:39:42

ditch:
isn't
abs(@salary - @fine) = (@salary - @fine)
slower than
Salary < @fine
??

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources