A domain error occurred.

Hi All,

I am trying to use the SQRT function to calculate a value. My problem is that in some instances the value is < 0 and then SQRT does not work. (A domain error occurred.) I need for the answer then to be NULL. I tried to do the following but no luck.


declare @fVal1 int
declare @fVal2 int

set @fVal1 = 9
set @fVal2 = 10

select case when ISNULL(SQRT(@fVal1-@fVal2),0) < 0
then 0 else SQRT(@fVal1-@fVal2) end



Any help would be much appreciated!

Kind regards
Sanette


SQL Developer
JustEnough Software Corporation
Walking on water and coding of a spec is easy, as long as both are frozen - A friend

SQRT(&lt;value&gt<img src='/community/emoticons/emotion-5.gif' alt='' /> doesn't return NULL if value &lt; 0, it just raises a domain error, and you cannot catch domain errors. You need to evaluate your two parameters and take it from there:<pre id="code"><font face="courier" size="2" id="code">declare @fVal1 int<br />declare @fVal2 int<br /><br />set @fVal1 = 9<br />set @fVal2 = 10<br /><br />IF (@fVal1-@fVal2) &lt; 0<br /> SELECT 0<br />ELSE<br /> SELECT SQRT(@fVal1-@fVal2)</font id="code"></pre id="code"><br />

Frank,<br /><br />True enough!<br /><br />Coming from a VBA background, I was anticipating the problem you would get in an IIf(&lt;condition&gt;, &lt;true_part&gt;, &lt;false_part&gt<img src='/community/emoticons/emotion-5.gif' alt='' /> statement, since VBA will evaluate both the true part and the false path and would therefore also throw an error.<br /><br />T-SQL is obviously much better behaved than VBA.[<img src='/community/emoticons/emotion-2.gif' alt='' />]

Thanks guys!!!

All sorted.

Sanette

SQL Developer
JustEnough Software Corporation
Walking on water and coding of a spec is easy, as long as both are frozen - A friend