2

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?

Colin 't Hart
  • 9,323
  • 15
  • 35
  • 43
coderman
  • 155
  • 1
  • 2
  • 7

4 Answers4

4

The STR function seems to be what you're looking for:

DECLARE @NumberOfDecimalPlaces tinyint = 3;
DECLARE @ActualValue decimal(16,5) =  3.50

SELECT FormattedActualValue = 
    STR(
        @ActualValue, 
        CAST(@ActualValue / 10 AS int) + @NumberOfDecimalPlaces + 2,
        COALESCE(@NumberOfDecimalPlaces,2)
    )
spaghettidba
  • 11,266
  • 30
  • 42
  • 1
    Nice, I guess I'd long forgotten that STR could use parameters there (wouldn't it be nice if all built-ins did this?). I shy away from STR generally because the output has to be a string and because you have to add that unintuitive / convoluted stuff to get it to round properly (like we do in math). So I guess it's pick your poison between that and dynamic SQL. – Aaron Bertrand Feb 15 '17 at 15:04
  • 1
    @AaronBertrand Regarding "all built-ins should support this" - hell, yeah! I wish! Unfortunately, I suppose it would be a devastating change to the parser. Regarding "output has to be a string": I think that's what the OP wants here, contrary to what is usually asked. – spaghettidba Feb 15 '17 at 15:10
  • @spaghettidba Thanks for the STR(). Couldn't have thought of it easily. I am aiming at creating a view and this looks like a lot of performance overhead. I need to weigh my options with both dynamic SQL and STR(). What do you suggest? – coderman Feb 16 '17 at 04:08
  • Dynamic SQL will generate a view definition only on the basis of current values and that would return erroneous results if the configured value of the decimal place would change later. I guess using STR() is my only option. Thanks. – coderman Feb 16 '17 at 04:51
3

You can't use parameters/variables for precision, so you'll need to use dynamic SQL for this. Performance overhead here is relatively close to zero.

DECLARE @NumberOfDecimalPlaces tinyint;

SET @NumberOfDecimalPlaces = 3;

DECLARE @sql nvarchar(max) = N'SELECT FormattedActualValue = 
  CONVERT(DECIMAL(16, $decimal_places$), 13.5457);';

SET @sql = REPLACE(@sql, N'$decimal_places$', COALESCE(@NumberOfDecimalPlaces,2));

EXEC sys.sp_executesql @sql;

After seeing Gianluca's answer I started to play with something else, which may or may not suit your needs (depending on whether you want trailing zeros when @DecimalPlaces > (decimal places in original value):

DECLARE @DecimalPlaces tinyint       = 5,
        @ActualValue   decimal(16,5) = 3.561735;

SELECT LTRIM(STR(ROUND(@ActualValue,@DecimalPlaces),20,@DecimalPlaces));
Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
  • Yes. I do need trailing zeros when @DecimalPlaces > (decimal places in original value). Otherwise ROUND() would have sufficed. – coderman Feb 16 '17 at 04:05
0

The FORMAT function accepts a wide variety of input types. The output can be formatted to the culture & locale of the user. It produces an nvarchar result.

Michael Green
  • 24,839
  • 13
  • 51
  • 96
0

This is a slight alteration to spaghettidba's answer. When specifying the number of digits to show, you need to do something other than devide the value by 10:

The STR function accepts 3 arguments:

STR( [Value],[Total Number of Didgits],[Decimal Places])

The correct calculation of [Total Number of Didgits] should be something like:

 LEN(CAST(ROUND([Value], 0) AS INT)) + [Decimal Places] + 1
 /* + 1 for the decimal point itself */

this will get the number of digits before the decimal place. E.g. 

DECLARE @NumberOfDecimalPlaces TINYINT = 3;
DECLARE @ActualValue DECIMAL(16, 5) =  .005643

SELECT FormattedActualValue = STR(@ActualValue, LEN(CAST(ROUND(@ActualValue, 0) AS INT)) + @NumberOfDecimalPlaces + 1, @NumberOfDecimalPlaces)
Thomas Hicks
  • 1