Zero if value is negative - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

Zero if value is negative

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-07-27 : 10:02:14

Hi all,
is there a more elegant way to write this script?

DECLARE @test DECIMAL
SET @test = -2
IF (@test < 0)
SET @test = 0
PRINT @test

I'm using SQL Server 2008 R2

Thanks in advance.

Luigi

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-27 : 10:20:24

DECLARE @test DECIMAL
SET @test = -2
SET @test = CASE WHEN @test < 0 THEN 0 ELSE @test END
PRINT @test

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-27 : 10:22:21

DECLARE @test DECIMAL
SET @test = -2
SET @test = isnull(nullif(sign(@test),-1),0)*@Test

Corey

I Has Returned!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-27 : 10:31:12

There's also:

DECLARE @test DECIMAL
SET @test = -2
SET @test = (ABS(@test)+@test)/2
PRINT @test

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-07-27 : 10:34:07

Perfect, good choices. Thank you all.

Luigi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-08-08 : 10:03:25

Also explicitely speicfy the length for DECIMAL. Otherwise the result may be unpredictable

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources