2

Consider the following function

CREATE FUNCTION dbo.SafeDivide
(
    @a numeric,
    @b numeric
)
RETURNS numeric
AS
BEGIN
    RETURN CASE
               WHEN @b = 0
                   THEN 0
               ELSE @a / @b
           END;
END;

Here type numeric was used but this code would be useful for any numeric types with any precision.

Is there a way to define and use such function?

I tried sql_variant type but it complains

Operand data type sql_variant is invalid for divide operator
  • Not in the language. Many functions just convert the arguments to ‘float’ and make the caller convert back. – David Browne - Microsoft May 14 at 15:57
  • @JoeObbish I'm looking for a type with maximum precision so any numeric type can be casted to it without losing precision – mnaoumov May 16 at 21:29
1

The error says what to do, that sql_variant type does not accept to operate the divide function.

I would suggest to use CONVERT/CAST to a specific data type, depending on your usage. Below I will use float:

DROP FUNCTION IF EXISTS dbo.SafeDivide
GO
CREATE FUNCTION dbo.SafeDivide
(
    @a sql_variant,
    @b sql_variant
)
RETURNS sql_variant
AS
BEGIN
     RETURN CASE
               WHEN @b = 0
                   THEN 0.00
               ELSE Convert(float, @a) / Convert(float, @b) 
           END;
END
GO
/*
select dbo.SafeDivide(4,4.4622434)
*/
1

I don't think that this is practical. Consider the following example:

DECLARE @big_value_for_u NUMERIC(38,0) = 99999999999999999999999999999999999999,
@small_value NUMERIC(38, 38) = 0.00000000000000000000000000000000000001;

What data type would you want the following expression to have?

SELECT @big_value_for_u / @small_value

NUMERIC has a max precision of 38 and FLOAT only has eight bytes. There must be some loss of precision with a numeric data type. Databases in general expect you to define your data type ahead of time. If you're saving the result of that division to a column or variable then you'll need to do that.

I suppose that you could convert everything to strings, roll your own division method, and give the output as a string. But what would you do with the result then?

  • Well, if you can write in plain SQL SELECT @big_value_for_u / @small_value, so I would like my helper function to work exactly the same way as division operator – mnaoumov May 18 at 15:03
  • @mnaoumov You can't: Msg 8115, Level 16, State 2, Line 4 Arithmetic overflow error converting expression to data type numeric. – Joe Obbish May 18 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.