表名改过来,试下?
create procedurce pro_insert
as
declare @allAmount double,@per double,@safeQty double,@typeid int
set xact_abort on
begin tran
select amount,avg_consume,sum(amount) into @mAmount,@avg_consume,@allAmount from tableName
set @per=@mAmount / @allAmount
if @per<=0.7 then @safteQty=@avg_consume * 7
@typeid=1
end if
if @per>0.9 then @safeQty=@avg_consume * 30
@typeid=3
end if
@safeQty=@avg_consume * 10
@typeid=2
insert into tableName2(对应字段列) values((select loginHospital,prod,price,consume from tableName),@mAmount,@per,@typeid,@safeQty)
commit tran