Click here to Skip to main content
12,633,073 members (33,616 online)
Rate this:
 
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.5K
v2
Comments
Ankur\m/ 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!
:)
  Permalink  
v2
Comments
Ali_100 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 7-Feb-13 2:51am
   
see updated solution...
Ali_100 7-Feb-13 4:44am
   
afterpoint is not has a range of 2-4 only,,,,
Aarti Meswania 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.161208.2 | Last Updated 7 Feb 2013
Copyright © CodeProject, 1999-2016
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