I have a MySQL function where I have a cast to DECIMAL like this
DECLARE len INT;
SET len = 10;
CAST((COUNT(*) * 1.5) AS DECIMAL(len))
However when I run it I get error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'len)
What I want is to use a variable len in AS DECIMAL() expression.
MySQL does not allow variables to be used to define data, so you cannot use a variable to indicate the precision or scale of a decimal data type.
All you can do is to create the appropriate sql command by string concatenation and issue it using a prepared statement.
The only catch with prepared statements is that they are not executed in the context of your function. So, if you plan to use the results later in your function, then you may encounter some issues.
You could use the maximum allowed precision value in your function instead of trying to dynamically set it in the code, or format the output on the application side to the correct number of digits.
Getting the desired number of decimals can be done through string manipulation.
With _value being the number, and _decimals the number of desired decimals:
DECLARE _result TEXT;
DECLARE _left TEXT;
DECLARE _right TEXT;
-- Ensures that the number have a decimal part
SET _value = CAST(ROUND(_value, _decimals) AS DECIMAL(65, 30));
-- Take all non-decimals
SET _left = SUBSTRING_INDEX(_value, ".", 1);
-- Take decimals, limited to _decimals characters
SET _right = RPAD(SUBSTRING_INDEX(_value, ".", -1), _decimals, "0");
-- Concatenate the result
IF CHAR_LENGTH(_right) THEN
SET _result = CONCAT(_left, ".", _right);
ELSE
SET _result = _left;
END IF;
