Professional Web Applications Themes

IsZero(value, 1) - Microsoft SQL / MS SQL Server

MSS2000 I am writing a UDF called IsZero to act similar to IsNull. My concern is that I think I need to know what numeric data type is being used so if the value is not zero, I am sure to return the correct datatype dynamically. This leads to four questions: 1) How can I 'generically' bring a number into the UDF not knowing the datatype coming in? Define the argument as a large float? I presume I cannot overload! 2) How can I 'generically' send the value out again, not losing the datatype that was brought in? 3) How ...

  1. #1

    Default IsZero(value, 1)

    MSS2000

    I am writing a UDF called IsZero to act similar to IsNull.

    My concern is that I think I need to know what numeric data type is being
    used so if the value is not zero, I am sure to return the correct datatype
    dynamically.

    This leads to four questions:

    1) How can I 'generically' bring a number into the UDF not knowing the
    datatype coming in? Define the argument as a large float? I presume I cannot
    overload!

    2) How can I 'generically' send the value out again, not losing the datatype
    that was brought in?

    3) How can I test the value to find the datatype sent in?

    4) Am I completely barking up the wrong tree?



    Jason Guest

  2. #2

    Default Re: IsZero(value, 1)

    "Jason" <sd.us> wrote in message
    news:%phx.gbl... 
    cannot 
    datatype 

    Since you cannot overload, this is not possible.
    Your return value will be converted into the return value of the UDF.

    Your two choices are
    -just use a big numeric type (eg decimal(38,18))
    -create one UDF for each type IsZero_int, IsZero_float, IsZero_decimal
    etc


    David





    David Guest

  3. #3

    Default Re: IsZero(value, 1)

    One of these alternatives should suit your needs better than a UDF. They
    first eliminates NULLs and the second propagates NULLs. Either of these
    should be much faster than a UDF.

    COALESCE(NULLIF(value,0),1)

    CASE value WHEN 0 THEN 1 ELSE value END

    For future ref, SQL_VARIANT can be used to handle variant data types.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Jason" <sd.us> wrote in message
    news:#phx.gbl... 
    cannot 
    datatype 


    David Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139