执行存储过程出现将 nvarchar 转换为数据类型 numeric 时出现算术溢出错误。

exec ycliek_update @user=N'admin',@chuangtm=N'customer',@bs=0,@kuangjm=N'dg1',@liem=N'id',@liek=77,@liex=0
go如执行这句就会出错,高手帮我看下这个存储过程是不是哪里写的不对,谢谢


存储过程如下

ALTER PROCEDURE [dbo].[ycliek_update]
@user nvarchar(20),

@chuangtm nvarchar(50),

@bs int,
@kuangjm nvarchar(50),
@liem nvarchar(50),
@liek decimal(18,2),
@liex int


AS
BEGIN

SET NOCOUNT ON;

declare @sql varchar(8000) -- Insert statements for procedure here
if(@bs=1)

set @sql=' delete from ycfileliek where chuangtm='''+@chuangtm+''' and yonh='''+@user+''' '
else


set @sql=' insert into ycfileliek(chuangtm, kuangjm, liem, liek, liex, xgshij,yonh)
values('''+@chuangtm+''','''+@kuangjm+''','''+@liem+''', '+@liek+','+@liex+',getdate(),'''+@user+''' )'

--print @sql
exec (@sql)


END



[解决办法]

SQL code

alter PROCEDURE [dbo].[ycliek_update]@user nvarchar(20),@chuangtm nvarchar(50),@bs int,@kuangjm nvarchar(50),@liem nvarchar(50),@liek decimal(18,2),@liex int    ASBEGIN SET NOCOUNT ON; declare @sql varchar(8000) -- Insert statements for procedure hereif(@bs=1)       set @sql=' delete from ycfileliek where chuangtm='''+@chuangtm+''' and yonh='''+@user+''' '  else      set @sql=' insert into ycfileliek(chuangtm, kuangjm, liem, liek, liex, xgshij,yonh)    values('''+@chuangtm+''','''+@kuangjm+''','''+@liem+''', '+rtrim(@liek)+','+rtrim(@liex)+',getdate(),'''+@user+''' )'   --print @sqlexec (@sql) END
[解决办法]
@liem nvarchar(50),
@liek decimal(18,2),

这两个变量与字符串相加时要需要转为字符串

用 cast/convert 或 rtrim/ltrim