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.
| Author |
Topic |
|
ThreePea
Yak Posting Veteran
83 Posts |
 Posted - 2003-07-17 : 08:15:23
|
Take the following two snippets of code. This one works: DECLARE @Test INT SET @Test = 0 SELECT CASE WHEN @Test > 0 THEN 10/@Test ELSE 0 END This one gives a divide by zero error:DECLARE @Test INT SET @Test = 0SELECT CASE WHEN @Test > 0 THEN SUM( 10/@Test ) ELSE 0END Why does the SUM keyword force the expression to be evaluated even though the logic never branches there?I'm running SQL Server 2000 sp3. Thanks.3P==================================================Tolerance is the last virtue of an immoral society. -- G.K. Chesterton |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-07-17 : 08:20:24
|
| The Sum() function probably has to be evaluated by the parser, the other expression you had doesn't. In any case, use the one that works. You can also:SELECT Sum(CASE WHEN @test>0 THEN 10/@test ELSE 0 END) |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-07-17 : 08:42:19
|
| SELECT SUM (CASE WHEN @Test>0 THEN 10 / @Test ELSE 0 END) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-17 : 10:17:21
|
Why are you even usinf SUM in that context? Didn't even know you could...DECLARE @Test INT SET @Test = 0SELECT CASE WHEN @Test > 0 THEN 10/@Test ELSE 0END Brett8-) |
|
|
|
|
|
|
Ok, why the divide by zero error?