Click here to Skip to main content
11,496,146 members (781 online)
The site is currently in read-only mode for maintenance. Posting of new items will be available again shortly.
See more: SQL conversion
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/ at 7-Feb-13 3:54am
The query seems wrong. The first CAST method should end after NUMERIC.

1 solution

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!
Smile | :)
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)

  Print Answers RSS
0 Dnyaneshwar@Pune 692
1 RyanDev 230
2 CHill60 163
3 Sascha Lefèvre 145
4 Sergey Alexandrovich Kryukov 124
0 Sergey Alexandrovich Kryukov 10,401
1 OriginalGriff 8,910
2 Sascha Lefèvre 3,899
3 Maciej Los 3,422
4 Richard Deeming 2,600

Advertise | Privacy | Mobile
Web04 | 2.8.150520.1 | Last Updated 7 Feb 2013
Copyright © CodeProject, 1999-2015
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