sql round function

Question

Hi i need to round one decimal value to deicmal places which is passes as parameter. By doing : select round(n,@a) from tbl_Test

where n is of type decimal 18,4 and @a=2 i got result but two zeros is added at the end. I want to get result without that zeros..but return value should be of ype decimal..not string.

Is it possible anything like : cast(round(n,@a) as decimal(18,@a))?

Answer 1

If you want only 2 decimals, you'll need to change the scale in the definition to 2. So, exactly as you supposed:

cast(round(n,@a) as decimal(18,@a))

MS SQL Server does not support the use of parameters in data type definitions. Now, "client" code does. So, an approach like this might work for you, ( either written in a db sproc with t-sql or in business layer with c# or whatever ).

use tempdb;
go
create 
--drop 
table tbl_test 
( n  decimal ( 18, 4 ) );
declare @n as decimal ( 18, 4 );
SET @n = 12.0006;
insert into tbl_test ( n ) values ( @n );
SET @n = 12.0060;
insert into tbl_test ( n ) values ( @n );
SET @n = 12.0600;
insert into tbl_test ( n ) values ( @n );
SET @n = 12.6000;
insert into tbl_test ( n ) values ( @n );

select 
    cast ( round ( n, 2 ) as decimal ( 18, 2 ) )
    , n
from tbl_test
order by n ASC

declare @var_scale integer;
set @var_scale = 2;
declare @sql nvarchar(max) = N'
select 
    cast ( round ( n, @a ) as decimal ( 18, @a ) )
    , n
from tbl_test
order by n ASC
'
set @sql = REPLACE ( @sql, '@a', @var_scale );
execute ( @sql );

---

But, much like Thomas said in the first comment to your question, without a pretty good explanation as to why, this would seem like pure folly. For instance, in C#, one would, as one consumed values in field n, simply round to 2 places. See this stackoverflow article. If the client is a Crystal Reports or Sql Server Reporting Services, you have the same sort of options-both have built in functions which perform the rounding for you. It may take some doing, but you can probably pass user defined scale values to them as well.