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
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)
*/
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?
