I want to format a number to display decimal places according to a configuration value specified in another table. If the configured value is NULL, the number of decimal places should default to 2 (hardcoded here) The following statement works fine except that for values like 3.50000 which returns 3.5 even when the configured value for 'NumberOfDecimalPlaces' is 2 or 3. I get that it is the desired behavior for ROUND()
ROUND([ActualValue], COALESCE([ConfigurationTable].[NumberOfDecimalPlaces], 2)) [FormattedActualValue]
As an alternative, I tried CONVERT AND CAST.
SELECT CAST ([ActualValue] AS NUMERIC(16, COALESCE([ConfigurationTable].[NumberOfDecimalPlaces], 2))
SELECT CONVERT(DECIMAL (16, COALESCE([ConfigurationTable].[NumberOfDecimalPlaces], 2)), [ActualValue])
Both of which err to Incorrect syntax near the keyword 'COALESCE'.
I understand that the second parameter to the datatype definition is not nullable and hence the error. What are my options? How can I achieve this with minimum performance overhead?
Best Answer
The STR function seems to be what you're looking for: