CREATE PROC p_ceshi
@fdnQuantity numeric(19,6),
@fdmAmount numeric(19,6)
AS
BEGIN
UPDATE tbStock SET fdnQuantity=fdnQuantity + CASE WHEN @fdnQuantity < 0 THEN '+' ELSE '-' END + Abs(@fdnQuantity),
fdmAmount=fdmAmount + CASE WHEN @fdmAmount < 0 THEN '+' ELSE '-' END + Abs(@fdmAmount)
WHERE fdlStockID=@fdlStockID
END 一直出现 将 varchar 转换为数据类型 numeric 时出现算术溢出错误。的错误,
应该是连接符号的时出的问题,可是,怎么改哟。。
@fdnQuantity numeric(19,6),
@fdmAmount numeric(19,6)
AS
BEGIN
UPDATE tbStock SET fdnQuantity=fdnQuantity + CASE WHEN @fdnQuantity < 0 THEN '+' ELSE '-' END + Abs(@fdnQuantity),
fdmAmount=fdmAmount + CASE WHEN @fdmAmount < 0 THEN '+' ELSE '-' END + Abs(@fdmAmount)
WHERE fdlStockID=@fdlStockID
END 一直出现 将 varchar 转换为数据类型 numeric 时出现算术溢出错误。的错误,
应该是连接符号的时出的问题,可是,怎么改哟。。
then 后面为什么跟的是符号呢?后面还加一个数字
@fdnQuantity numeric(19,6),
@fdmAmount numeric(19,6),
@fdlStockID int
AS
BEGIN
UPDATE tbStock SET fdnQuantity=fdnQuantity - @fdnQuantity,
fdmAmount=fdmAmount - @fdmAmount
WHERE fdlStockID=@fdlStockID
END
@fdnQuantity numeric(19,6),
@fdmAmount numeric(19,6)
AS
BEGIN
UPDATE tbStock SET fdnQuantity=fdnQuantity +
CASE WHEN @fdnQuantity < 0 THEN @fdnQuantity ELSE @fdnQuantity*-1 END ,
fdmAmount=fdmAmount + CASE WHEN @fdmAmount < 0 THEN @fdmAmount ELSE @fdmAmount*-1 END
WHERE fdlStockID=@fdlStockID
END
这个效果?
UPDATE tbStock
SET fdnQuantity=
CASE WHEN @fdnQuantity < 0 THEN fdnQuantity + Abs(@fdnQuantity) ELSE fdnQuantity - Abs(@fdnQuantity) END,
fdmAmount= CASE WHEN @fdmAmount < 0 THEN fdmAmount + Abs(@fdmAmount) ELSE fdmAmount - Abs(@fdmAmount) END
WHERE fdlStockID=@fdlStockID试试这样
fdmAmount=fdmAmount + @fdmAmount
WHERE fdlStockID=@fdlStockID
你的传入参数本来就有正负了。没必要你自己再去判断正负然后+/-绝对值。