Checking Negative Number

Question

Post reply

Checking Negative Number

CrazyMan

SSChampion

Points: 12560
More actions
June 8, 2009 at 8:07 am

#201244

Hi All
I need to check whether a passed number is positive or negative, is there any predefined functions on SQL 2005 to do this, apart from checking the value <0.
I am trying to use this on the select statement, is there something like isnull funciton on SQL that i can use to get the result

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26979 More actions · Answer 1

You could use the SIGN function, but I can't think why you would use this in preference to "<0"

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

I think the sign function may slow things down as it would result in a function being run on a column in your where clause which will affect the use of indexes.

Is there any reason why you don't want to use <0?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

CrazyMan SSChampion Points: 12560 More actions · Answer 3

Thanks Guys

I can use it, but i am not sure how to use it on the below Query

SELECT MAX(p.Col1) - SUM(CASE WHEN s.Col2 > s.Col1
THEN s.Col2 - s.Col1
ELSE 0
END) as Result1,
SUM(CASE WHEN s.S1 = @S1 AND
s.Col1 > s.Col2
THEN s.Col1 - s.Col2
ELSE 0
END) as Result2
FROM Table1 (NOLOCK)
INNER JOIN S1 s (NOLOCK)
ON s.U1 = p.U1
WHERE (p.U1 = @U1)

My condition is that if Result1 or 2 returns a negative number then i need to return 0 else the result

Cheers

Julie Hargraves SSCarpal Tunnel Points: 4457 More actions · Answer 4

Assuming this is in a stored proc, I think the easiest way to do what you're asking is as follows:

DECLARE @Result1 INT

DECLARE @Result2 INT

SELECT @Result1 = MAX(p.Col1) - SUM(CASE WHEN s.Col2 > s.Col1

THEN s.Col2 - s.Col1

ELSE 0

END),

@Result2 = SUM(CASE WHEN s.S1 = @S1 AND

s.Col1 > s.Col2

THEN s.Col1 - s.Col2

ELSE 0

END)

FROM Table1 (NOLOCK)

INNER JOIN S1 s (NOLOCK)

ON s.U1 = p.U1

WHERE (p.U1 = @U1)

SELECT CASE WHEN @Result1 < 0 THEN 0 ELSE @Result1 END AS Result1

, CASE WHEN @Result2 < 0 THEN 0 ELSE @Result2 END AS Result2

CrazyMan SSChampion Points: 12560 More actions · Answer 5

Thanks mate,This is what exactly i have done now in my SP, but i am quite interested to know if there are any other ways to incorporate this on the select statement.

The main reason i am a bit concerned is that some where on MS fourm i remember reading that its not good to assign results to variables inside SP, i may be wrong

Gatekeeper SSCommitted Points: 1633 More actions · Answer 6

See the last statement to lose the case statements.

DECLARE @Result1 INT

DECLARE @Result2 INT

SELECT @Result1 = MAX(p.Col1) - SUM(CASE WHEN s.Col2 > s.Col1

THEN s.Col2 - s.Col1

ELSE 0

END),

@Result2 = SUM(CASE WHEN s.S1 = @S1 AND

s.Col1 > s.Col2

THEN s.Col1 - s.Col2

ELSE 0

END)

FROM Table1 (NOLOCK)

INNER JOIN S1 s (NOLOCK)

ON s.U1 = p.U1

WHERE (p.U1 = @U1)

SELECT

@Result1 = MAX(Result1),

@Result2 = MAX(Result2)

FROM

(SELECT 0 as Result1, 0 as Result2

UNION ALL

SELECT @Result1, @Result2

) as a

/* Anything is possible but is it worth it? */

Checking Negative Number

Cookies on SQLServerCentral