question

Hello,

I would like the option of choosing a precision scale for numeric values. My database holds most values as numeric(10,3) however I propose to have the customer choose how many decimal places they want to see. The following code illustrates my issue:

DECLARE @RET TABLE (BATCH_NUMBER VARCHAR(100),
                 QTY numeric(10,3),
                 UOM varchar(10),
                 scale int
                 )

       INSERT INTO @RET
        SELECT '12345','3.500','EA',2

I tried the following,

CASE scale
           WHEN 0 THEN cast(qty AS numeric(10,0))
           WHEN 1 THEN cast(qty AS numeric(10,1))
           WHEN 2 THEN cast(qty AS numeric(10,2))
       ELSE cast(qty AS numeric(10,3))
       END as 'Qty'

However this style will always choose the highest amount of precision, regardless of the scale value. It's the same if i try 'WHEN scale = 0' syntax style.

This option below works, when qty is a float,

round(cast (qty AS float) * power(10, scale), 0) / power(10, scale) AS 'Qty',

However it will drop trailing zero's, and It would be important that values displayed line up correctly. Is there a better way to implement a formatting strategy?

Thanks, Dallas