SQL Server Performance

A domain error occurred.

Discussion in 'General Developer Questions' started by SanetteWessels, Feb 8, 2005.

  1. SanetteWessels New Member

    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
  2. Adriaan New Member

    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 />
  3. FrankKalis Moderator

  4. Adriaan New Member

    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=':D' />]
  5. FrankKalis Moderator

    I use VBA myself a lot here. Mainly Excel and Outlook. I can't say I like it, but I don't hate it either. It's just a tool to get the job done. After rereading the thread, I realize that NULLs are present, so here's the revamped version.


    SELECT
    CASE WHEN @fVal1-@fVal2 < 0 THEN NULL ELSE SQRT(@fVal1-@fVal2) END

    Returns NULL in case the result is < 0 or when one variable is NULL

    --
    Frank
    http://www.insidesql.de
  6. SanetteWessels New Member

    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

Share This Page