生成列名时出错 [英] error while casting a column name

问题描述

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.

推荐答案

这种方式..

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

这篇关于生成列名时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！