Using SELECT in CAST function of SQL

Question

I am working on an order management web project in C# which can be provided to multiple clients.

In 'orders' table, I have a field 'Qty' of 4 decimals. Now not all clients may require 4 decimal places, so I have a table 'Config' which holds this value in field 'QtyDecimal'.

Presently what I am doing to show the correct number of decimals is something like this:

string xQtyDec = getQtyDecimalFromConfig();
sqlCommand = "Select CAST(Qty as numeric(18," + xQtyDec + ")) [Quantity] from orders";
...

I was wondering if this could be accomplished in SQL itself, something like

SELECT CAST(Qty as numeric(18, (SELECT QtyDecimal FROM config))) [Quantity] from orders

Is this possible?

I would recomend you to do the rounding in C# instead of the database, you sould always round as late as possible, i.e. when you show the values to the user. — Thorarins
– Thorarins, Commented Sep 6, 2016 at 7:04
yes that is right, but i thought why type 4 lines when you can finish it in 1 ! @Thorarins — Jones Joseph
– Jones Joseph, Commented Sep 6, 2016 at 7:10

Serg · Accepted Answer · 2016-09-06 07:31:42Z

1

According to Sql sever syntax , see https://msdn.microsoft.com/en-us/library/ms174979.aspx data type parameters (precision and scale) must be literals. They couldn't be expressions. You'd better do things like that on client side. Alternatively use round(), cast result to max possible scale (4) and strip extra zeroes from string representation

declare @s int = 2; -- scale config parameter, = 0..4

select t.col, substring(calc.v, 1, len(calc.v) -(4-@s))
from (--- sample data
      values (12345.06789) 
        , (8765000.1)
     ) t(col)
cross apply (
    select v = cast(cast(round(col, @s) as numeric (18,4)) as varchar(20))
    ) calc

answered Sep 6, 2016 at 7:31

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jones Joseph Over a year ago

Awesome, just a long one one but this did it. i assigned @s to QtyDecimal from Config and it worked !

Unnikrishnan R · Accepted Answer · 2016-09-06 09:05:06Z

1

In SQL Server,you need to build a dynamic query to achieve the result.

DECLARE @Scale NVARCHAR(50)
SELECT  @scale= QtyDecimal FROM config
DECLARE @sql NVARCHAR(500)='SELECT CAST(Qty as numeric(18, ' +@scale+')) [Quantity] from orders'
EXEC (@sql)

edited Sep 6, 2016 at 9:05

answered Sep 6, 2016 at 7:36

Unnikrishnan R

5,0511 gold badge15 silver badges21 bronze badges

1 Comment

Jones Joseph Over a year ago

this works too.. however the @sql has length more than declared.. its more than 50

Frits · Accepted Answer · 2016-09-06 06:54:10Z

0

Yes, that is possible, but you should rewrite your query as:

SELECT CAST(orders.Qty as numeric(18, (config.QtyDecimal))) AS [Quantity] from orders, config
WHERE {some selection on config table to get the correct QtyDecimal}

answered Sep 6, 2016 at 6:54

Frits

3141 silver badge12 bronze badges

2 Comments

Jones Joseph Over a year ago

Sorry but i dint quite get it, should i do this Select CAST(...............) as [Quantity] from orders where select QtyDecimal from Config . Also if i do this it throws an error at '(config.QtyDecimal)' - 'expecting an integer'

Frits Over a year ago

Indeed, numeric does only accept integers. Is using ROUND() an option?

Ben · Accepted Answer · 2016-09-06 09:10:22Z

The best option is not to do this, but rather to do it in C# at the point where you convert the dataset for display or download. However if you need to do it in SQL you can use one of these approaches.

Approach 1:

Select CAST(CASE (select xQtyDecimal from Config)
   WHEN 1 then CAST(FieldName as NUMERIC(10,1)
   WHEN 2 then CAST(FieldName as NUMERIC(10,2)
   WHEN 3 then CAST(FieldName as NUMERIC(10,3)
   WHEN 4 then CAST(FieldName as NUMERIC(10,4)
   WHEN 5 then CAST(FieldName as NUMERIC(10,5)
   ELSE FieldName END AS NUMERIC(10,6)) As FieldNameAlias
From TableName

Pro: Control behaviour in each case exactly
Con: Each case must be written out.

Approach 2:

SELECT CAST(CAST(FieldName * POWER(10, (select xQtyDec from config)) as BIGINT) AS NUMERIC(20,10))/POWER(10, (select xQtyDec from config))
FROM TableName

Pro: Any number of decimal places can be coped with
Con: Rounds down, can't control e.g. banker's rounding.

Collectives™ on Stack Overflow

Using SELECT in CAST function of SQL

4 Answers 4

1 Comment

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related