MySQL cast to DECIMAL with variable precision

Question

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.

Answer 1

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.