Checking Negative Number
-
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 🙂
-
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
-
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:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
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
-
Assuming this is in a stored proc, I think the easiest way to do what you're asking is as follows:
DECLARE @Result1 INTDECLARE @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
-
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 🙂
-
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? */
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply