Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL conversion
Set FormattedValue = CASE 
                         When rg.ReferredFieldType = 'NUM' Then              
                              CAST(#PointedMslValues.Value AS         
                              NUMERIC(18,cAST(rg.ReferredDecimalPoints) )
 
                     --more cases...
END
I am generating a dynamic query, which is giving error.
Actually it requires integer value, while casting not a column name.
Posted 6-Feb-13 20:35pm
Ali_1001.3K
v2
Comments
Ankur\m/ at 7-Feb-13 3:54am
   
The query seems wrong. The first CAST method should end after NUMERIC.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

this way..
declare @AfterPoint varchar(3);
	set @Afterpoint=2;
declare @val numeric(18,9);
	set @val=1234.89535656;
declare @SQLQuery varchar(max);
	Set @SQLQuery = 'select cast(' + convert(varchar(100), @val) + ' as numeric(18,' + @AfterPoint + '))';
exec(@SQLQuery);
 
other solution for tabular format
declare @val numeric(18,9);
    set @val=1234.89535656;
 
declare @a varchar(max);
set @a='';
 
with a as
(
select 'NUM' as ftype, '2' as Afterpoint
union all
select 'NUM' as ftype, '3' as Afterpoint
union all
select 'NUM' as ftype, '4' as Afterpoint
)
 
select @a = @a + ' select ' + Afterpoint + ' as Afterpoint,convert(varchar(100),CAST(' + convert(varchar(100), @val) + ' AS NUMERIC(18,'+ Afterpoint + '))) as FormattedVal' +  ' union all '
from a
select @a=substring(@a,1,len(@a)-10);
exec(@a);
Happy Coding!
Smile | :)
  Permalink  
v2
Comments
Ali_100 at 7-Feb-13 2:13am
   
thanks for the answer ,,, but as u are equating @Afterpoint=2;,, actually here @Afterpoint=is the column value which could b every number,,,not always 2
Aarti Meswania at 7-Feb-13 2:51am
   
see updated solution...
Ali_100 at 7-Feb-13 4:44am
   
afterpoint is not has a range of 2-4 only,,,,
Aarti Meswania at 7-Feb-13 4:56am
   
then how much it will?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.1411022.1 | Last Updated 7 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100