从数据类型varchar转换为numeric时出错

[夜未眠风已息]

创建表
create table test_num(
tid int not null primary key,
tbigint bigint,
tint int,
tsmallint smallint,
ttinyint tinyint,
tbit bit,
tdecimal decimal(9,2),
tnumeric numeric(10,2),
tmoney money,
tsmallmoney smallmoney
);

创建如下触发器
/****** 对象: Trigger [dbo].[dbctrii#test_num] 脚本日期: 04/06/2012 13:01:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[dbctrii#test_num] ON [dbo].[test_num]
WITH EXEC AS CALLER INSTEAD OF INSERT
AS
SET NOCOUNT ON;
declare @v_sql varchar(8000);
declare @v_value varchar(max);
set @v_value='';
declare @v_sqltext varchar(max);
set @v_sqltext='';

declare @v_tid int ;
declare @v_tbigint bigint ;
declare @v_tint int ;
declare @v_tsmallint smallint ;
declare @v_ttinyint tinyint ;
declare @v_tbit bit ;
declare @v_tdecimal decimal(9,2) ;
declare @v_tnumeric numeric(10,2) ;
declare @v_tmoney money ;
declare @v_tsmallmoney smallmoney ;

select @v_tid=tid from inserted;
select @v_tbigint=tbigint from inserted;
select @v_tint=tint from inserted;
select @v_tsmallint=tsmallint from inserted;
select @v_ttinyint=ttinyint from inserted;
select @v_tbit=tbit from inserted;
select @v_tdecimal=tdecimal from inserted;
select @v_tnumeric=tnumeric from inserted;
select @v_tmoney=tmoney from inserted;
select @v_tsmallmoney=tsmallmoney from inserted;
IF @v_tid!=''
BEGIN
SET @v_value=@v_value+CONVERT(VARCHAR(100),@v_tid);
print @v_value;
END
IF @v_tbigint!=''
BEGIN
SET @v_value=@v_value+','+CONVERT(VARCHAR(100),@v_tbigint);
print @v_value;;
END
IF @v_tint!=''
BEGIN
SET @v_value=@v_value+','+CONVERT(VARCHAR(100),@v_tint);
print @v_value;
END
IF @v_tsmallint is not null
BEGIN
SET @v_value=@v_value+','+CONVERT(VARCHAR(100),@v_tsmallint);
print @v_value;
END
IF @v_ttinyint!=''
BEGIN
SET @v_value=@v_value+','+CONVERT(VARCHAR(100),@v_ttinyint);
print @v_value;
END
IF @v_tdecimal!=''
BEGIN
SET @v_value=@v_value+','+CONVERT(VARCHAR(100),@v_tdecimal);
print @v_value;
END
IF @v_tnumeric!=''
BEGIN
SET @v_value=@v_value+','+CONVERT(VARCHAR(100),@v_tnumeric);
print @v_value;
END
IF @v_tmoney!=''
BEGIN
SET @v_value=@v_value+','+CONVERT(VARCHAR(100),@v_tmoney);
print @v_value;
END
IF @v_tsmallmoney!=''
BEGIN
SET @v_value=@v_value+','+CONVERT(VARCHAR(100),@v_tsmallmoney);
print @v_value;
END

SET @v_sqltext='insert into [dbo].[test_num]([tid],[tbigint],[tint],[tsmallint],[ttinyint],[tbit],[tdecimal],[tnumeric],[tmoney],[tsmallmoney]) values('+@v_value+')';
EXEC coffer.Pro_AInsert @@spid,1,@v_sqltext;
print @v_sqltext;
return;


执行insert
insert into test_num values(1,23456543245,2345645,3452,157,1,79089.24,23456.45,203685477.584,7348.367);

就会报错：触发器里这一行
（SET @v_value=@v_value+','+CONVERT(VARCHAR(100),@v_tsmallint);
print @v_value;）
从数据类型varchar转换为numeric时出错
但触发器里并没有做这个转换啊
求指教

[Vidor]

但触发器里并没有做这个转换啊

----------

真的吗

IF @v_tdecimal!=''

这不用隐式转换啊？

[Vidor]

IF判断，全部改为数值型的。

-- NULL这样判断
IF @v_tdecimal is null

[勿勿]

从数据类型varchar转换为numeric时出错


这个出错了