进销存存储过程
create database Fruit create table fruit ( Ids varchar(50), Name varchar(50), Price decimal(18,2), Source varchar(50), Stack int, Numbers int, Image varchar(50) ) insert into fruit values('k001','苹果',2.4,' 烟台',2,91,'image/0.gf') insert into fruit values('k002','菠萝',1.4,' 广东',3,74,'image/1.gf') insert into fruit values('k004','葡萄',2.4,' 新疆',2,98,'image/3.gf') insert into fruit values('k006','蟠桃',1.4,' 蟠桃园',6,3,'image/5.gf') insert into fruit values('k007','香蕉',2.4,' 济南',5,100,'image/6.gf') select*from fruit --进销存存储过程 alter proc JinChuKu @Ids varchar(50), @Name varchar(50), @Price decimal(18,2), @Source varchar(50), @Stack int, @Numbers int, @Image varchar(50) as if @Numbers>0 --代表进货 begin declare @shiok int --定义一个变量 select @shiok=Numbers from fruit where Ids=@Ids --用这一个变量来接收输入@Ids所对应的数量 if @shiok>0 --如果有这种货,就直接修改库存 begin update fruit set Numbers=@Numbers+Numbers where Ids=@Ids --修改数量以输入的@Ids所对应的那一行为准 return 2--进货成功 end else --没有这种货,添加这种货 begin insert into fruit values(@Ids,@Name,@Price,@Source,@Stack,@Numbers,@image) return 1--'添加品种成功' end end else--代表出货 begin declare @isok int select @isok=Numbers from fruit where Ids=@Ids if @isok>0 --有这种货,就出货 begin if @isok>ABS(@Numbers) --库存的大于出货的 begin update fruit set Numbers=@Numbers+Numbers where Ids=@Ids return 0--'出货成功' end else --库存的小于要出货的 begin return -1--'有货,但不足' end end else --没有这种货 begin return -2--'直接没有这种货' end end go declare @a int exec @a=JinChuKu 'k001','苹果',2.4,'烟台',2,-10,'image/0.gf' print @a select *from fruit