CODEWITHSUNDEEP
mysqldecimalfinancemysql-function
Ram
Ram

Reputation: 284

In mysql user defined function how to set dynamic return data type

In mysql user defined function how to set dynamic return data type based on function execution. 

DELIMITER $$
CREATE FUNCTION financeNumberFormat(number double) RETURNS DECIMAL(10,5)
    DETERMINISTIC
BEGIN
    DECLARE decpoint int(10);

    SELECT c_maximum_decimal_values INTO decpoint
    FROM mst_xxx
    WHERE 1 LIMIT 1;    


    SET decpoint = FORMAT(number,decpoint); 

 RETURN (decpoint);
END

My output is coming like below. 

financeNumberFormat(5.345)  
5.000

The decpoint may vary, so return value should be based on that decimal point. Is there a way to achieve it.? Thanks in advance.

Upvotes: 1

Views: 329

Answers (1)

Rick James
Rick James

Reputation: 142296

First of all, FORMAT returns a string, since it can contain commas (thousands-separators). So, you may as return VARCHAR(99). By the same token, you may as well feed in strings unless your numbers are really DOUBLE.

Note that going from DECIMAL(m,n) to DOUBLE, then rounding (via FORMAT)d to some number of decimal places is doing 2 roundings, one more than you really need.

Another note: In many situations in MySQL (not all), a string containing numeric data is just as good as DOUBLE or DECIMAL. (The notable exception is with WHERE varchar_col = 1234 will fail to use INDEX(varchar_col).)

Upvotes: 1

Related Questions