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

执行存储过程出现将 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 [email protected]+''' and [email protected]+''' '
  else

   
  set @sql=' insert into ycfileliek(chuangtm, kuangjm, liem, liek, liex, xgshij,yonh) 
  values([email protected]+''',[email protected]+''',[email protected]+''', [email protected]+',[email protected]+',getdate(),[email protected]+''' )'
 
 --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 [email protected]+''' and [email protected]+''' '  else      set @sql=' insert into ycfileliek(chuangtm, kuangjm, liem, liek, liex, xgshij,yonh)    values([email protected]+''',[email protected]+''',[email protected]+''', '+rtrim(@liek)+','+rtrim(@liex)+',getdate(),[email protected]+''' )'   --print @sqlexec (@sql) END
------解决方案--------------------
@liem nvarchar(50),
@liek decimal(18,2),

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

用 cast/convert 或 rtrim/ltrim