我在做一个苗木库存管理系统,因为我们公司每天都有大量的苗木信息从外面采集回来,包括苗木的类别 名称 价格 规格 供应商信息 苗木等级
每种苗木的价格等信息都是不一样的,同一名称的苗木可能属于多个类别,
tb_GoodsName:苗木名称表
tb_Storage:苗木类别表
tb_Class_Goods:类别—名称映射表(多对多)
tb_InStore:库存信息表(采集回来的信息存放到这个表中,存放苗木的类别ID、名称ID、供应商ID等信息,链接其他表)
我在录入的时候 根据输入的苗木名称自动判断tb_GoodsName表中是否已存在,如果存在返回GoodsID,如果不存在:插入数据 然后返回值
这是语句
ALTER PROCEDURE dbo.InsertIntoStore
@GoodsName varchar(50),
@StoreName int,
@xiongjing varchar(50),
@dijing varchar (50),
@pengjing varchar (50),
@gaodu varchar (50),
@fenzhi varchar (50),
@GoodsUnit int,
@GoodsNum bigint,
@GoodsPrice decimal(18,2),
@ShouJia decimal(18,2),
@HandlePeople varchar (20),
@ISRemark varchar (1000),
@Pinzhi int,
@GYid int,
@IsGood bit,
@IsBareRoot bit
AS
----判断tb_GoodsName中是否已经存在要插入的名称,如果存在则取得GoodsId,不存在则插入后取得GoodsId
if exists (select GoodsId from tb_GoodsName where GoodsName=@GoodsName)
begin
declare @sql nvarchar(1000)
declare @gid int
set @sql= 'select @gid=GoodsId from tb_GoodsName where GoodsName=@GoodsName'
exec(@sql)
insert into tb_InStore(GoodsName,StoreName,
xiongjing,dijing,pengjing,gaodu,fenzhi,GoodsUnit,GoodsNum,GoodsPrice,shoujia,HandlePeople,ISRemark,pinzhi,PrName,isgood,isbareroot)
values(@gid,@StoreName,@xiongjing,@dijing,@pengjing,@gaodu,@fenzhi,@GoodsUnit,@GoodsNum,
@GoodsPrice,@ShouJia,@HandlePeople,@ISRemark,@Pinzhi,@GYid,@IsGood,@IsBareRoot)
end
else
begin
---不存在时
insert into tb_GoodsName(GoodsName) values(@GoodsName)
select @@identity
---在类别-名称映射表中插入映射关系
insert into tb_Class_Goods(GoodsId,ClassId) values(@@identity,@StoreName)
---在存储商品的表中插入数据
insert into tb_InStore(GoodsName,StoreName,
xiongjing,dijing,pengjing,gaodu,fenzhi,GoodsUnit,GoodsNum,GoodsPrice,shoujia,HandlePeople,ISRemark,pinzhi,PrName,isgood,isbareroot)
values(@@identity,@StoreName,@xiongjing,@dijing,@pengjing,@gaodu,@fenzhi,@GoodsUnit,@GoodsNum,
@GoodsPrice,@ShouJia,@HandlePeople,@ISRemark,@Pinzhi,@GYid,@IsGood,@IsBareRoot)
end
RETURN