Click here to Skip to main content
13,296,407 members (48,363 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Set FormattedValue = CASE 
                         When rg.ReferredFieldType = 'NUM' Then              
                              CAST(#PointedMslValues.Value AS         
                              NUMERIC(18,cAST(rg.ReferredDecimalPoints) )
                     --more cases...

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
Ankur\m/ 7-Feb-13 3:54am
The query seems wrong. The first CAST method should end after NUMERIC.

1 solution

Rate this: bad
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 + '))';

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);

Happy Coding!
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 |
Web02 | 2.8.171207.1 | Last Updated 7 Feb 2013
Copyright © CodeProject, 1999-2017
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