cast( xxx as decimal(xx,2)) cast( xxx as decimal(xx,3))
declare @t table (id numeric(7,4)) insert into @t select 100 union all select 123.1455select cast(id as decimal(18,2)) from @t /* 100.00 123.15 */
不好办,除非你函数把返回值写成varchar型,函数不能返回多个类型值。
我写了一个函数,但执行时提示错误:CREATE function num4char(@njob numeric(20,6),@len int) returns varchar(40) as begin declare @cnum varchar(20),@sql nvarchar(1000) set @sql = 'select @cnum = ltrim(cast('+ltrim(@njob)+' as numeric(10,'+ltrim(@len)+'))) ' exec sp_executesql @sql,N'@cnum varchar(20) output',@cnum output return @cnum end 错误信息: 服务器: 消息 557,级别 16,状态 2,过程 num4char,行 7 只有函数和扩展存储过程才能从函数内部执行。
--一个笨方法,如果小数位数不是太大的化,多写几个if语句判断一下 declare @a int ,@b int --小数位数 set @a=100 set @b=2if @b=1 set @a=cast(@a as decimal(18,1)) if @b=2 set @a=cast(@a as decimal(18,2)) . . . . if @b=10 set @a=cast(@a as decimal(18,10))
是出错,因为 @b=1时 cast(@ as numeric(10,@b)会出错,不允许使用@b,必须指定某个int常数
函数里不能exec的declare @t table (col varchar(20)) insert into @t select '2.343' union all select '1.700' union all select '8.32' union all select '9.2211' union all select '10.89'declare @i int;set @i=2 select round(col,@i) from @t /* 2.34 1.7 8.32 9.22 10.89 */ --这样行不行?
create table test0426(id numeric(7,4)) insert into test0426 select 100 union all select 123.1455 union all select 23.222declare @i int ;set @i=1 declare @j int ;set @j=2 declare @s varchar(200) set @s='select cast(id as decimal(18,'+ cast(@i as varchar(4))+')),cast(id as decimal(18,'+ cast(@j as varchar(4))+')) from test0426' exec(@s) /* 100.0 100.00 123.1 123.15 23.2 23.22 */ drop table test0426
cast( xxx as decimal(xx,3))
declare @t table (id numeric(7,4))
insert into @t
select 100 union all
select 123.1455select cast(id as decimal(18,2)) from @t
/*
100.00
123.15
*/
returns varchar(40)
as
begin
declare @cnum varchar(20),@sql nvarchar(1000)
set @sql = 'select @cnum = ltrim(cast('+ltrim(@njob)+' as numeric(10,'+ltrim(@len)+'))) '
exec sp_executesql @sql,N'@cnum varchar(20) output',@cnum output
return @cnum
end
错误信息:
服务器: 消息 557,级别 16,状态 2,过程 num4char,行 7
只有函数和扩展存储过程才能从函数内部执行。
--一个笨方法,如果小数位数不是太大的化,多写几个if语句判断一下
declare @a int ,@b int --小数位数
set @a=100
set @b=2if @b=1
set @a=cast(@a as decimal(18,1))
if @b=2
set @a=cast(@a as decimal(18,2))
.
.
.
.
if @b=10
set @a=cast(@a as decimal(18,10))
insert into @t
select '2.343' union all
select '1.700' union all
select '8.32' union all
select '9.2211' union all
select '10.89'declare @i int;set @i=2
select round(col,@i) from @t
/*
2.34
1.7
8.32
9.22
10.89
*/
--这样行不行?
create table test0426(id numeric(7,4))
insert into test0426
select 100 union all
select 123.1455 union all
select 23.222declare @i int ;set @i=1
declare @j int ;set @j=2
declare @s varchar(200)
set @s='select cast(id as decimal(18,'+
cast(@i as varchar(4))+')),cast(id as decimal(18,'+
cast(@j as varchar(4))+')) from test0426'
exec(@s)
/*
100.0 100.00
123.1 123.15
23.2 23.22
*/
drop table test0426