执行存储过程出现将 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  
 
 
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+''', '+rtrim(@liek)+','+rtrim(@liex)+',getdate(),'''+@user+''' )'
  
 --print @sql
exec (@sql)
 

END

------解决方案--------------------
@liem nvarchar(50),
@liek decimal(18,2),

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

用 cast/convert 或 rtrim/ltrim