Click here to Skip to main content
11,584,612 members (63,610 online)
Rate this: bad
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...
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 19:35pm
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
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!
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 OriginalGriff 430
1 Sergey Alexandrovich Kryukov 367
2 Dave Kreskowiak 260
3 Richard Deeming 220
4 virusstorm 194
0 OriginalGriff 1,703
1 Sergey Alexandrovich Kryukov 1,267
2 Abhinav S 711
3 Dave Kreskowiak 687
4 Suvendu Shekhar Giri 568

Advertise | Privacy | Mobile
Web03 | 2.8.150707.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