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),
@ISRe 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,ISRe,pinzhi,PrName,isgood,isbareroot)
values(@gid,@StoreName,@xiongjing,@dijing,@pengjing,@gaodu,@fenzhi,@GoodsUnit,@GoodsNum,
@GoodsPrice,@ShouJia,@HandlePeople,@ISRe,@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,ISRe,pinzhi,PrName,isgood,isbareroot)
values(@@identity,@StoreName,@xiongjing,@dijing,@pengjing,@gaodu,@fenzhi,@GoodsUnit,@GoodsNum,
@GoodsPrice,@ShouJia,@HandlePeople,@ISRe,@Pinzhi,@GYid,@IsGood,@IsBareRoot)
end
RETURN这段代码报错 select 附近有语法错误 ,经测试是这行set @sql= 'select @gid=GoodsId from tb_GoodsName where GoodsName=@GoodsName'
错误,麻烦各位指教!PS:我在做一个库存管理系统,我想要每种商品的名称在数据库中存在一个表中,录入库存的时候,
商品名称已存在:读取数据库中商品的ID,
商品名称不存在:插入名称到商品名称表,并插入类别、名称映射关系到映射表
请教各位大侠我这样的思路对不对啊?
@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),
@ISRe 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,ISRe,pinzhi,PrName,isgood,isbareroot)
values(@gid,@StoreName,@xiongjing,@dijing,@pengjing,@gaodu,@fenzhi,@GoodsUnit,@GoodsNum,
@GoodsPrice,@ShouJia,@HandlePeople,@ISRe,@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,ISRe,pinzhi,PrName,isgood,isbareroot)
values(@@identity,@StoreName,@xiongjing,@dijing,@pengjing,@gaodu,@fenzhi,@GoodsUnit,@GoodsNum,
@GoodsPrice,@ShouJia,@HandlePeople,@ISRe,@Pinzhi,@GYid,@IsGood,@IsBareRoot)
end
RETURN这段代码报错 select 附近有语法错误 ,经测试是这行set @sql= 'select @gid=GoodsId from tb_GoodsName where GoodsName=@GoodsName'
错误,麻烦各位指教!PS:我在做一个库存管理系统,我想要每种商品的名称在数据库中存在一个表中,录入库存的时候,
商品名称已存在:读取数据库中商品的ID,
商品名称不存在:插入名称到商品名称表,并插入类别、名称映射关系到映射表
请教各位大侠我这样的思路对不对啊?
declare @gid int
set @sql= 'select @gid=GoodsId from tb_GoodsName where GoodsName=@GoodsName'
exec(@sql)--> declare @sql nvarchar(1000)
declare @gid int
set @sql= N'select @gid=GoodsId from tb_GoodsName where GoodsName=@GoodsName'
exec sp_executesql @sql ,N'@gid int output',@gid output
declare @gid int
set @sql= N'select @gid=GoodsId from tb_GoodsName where GoodsName=''' + @GoodsName+ ''''
exec sp_executesql @sql ,N'@gid int output',@gid output或者declare @sql nvarchar(1000)
declare @gid int
set @sql= N'select @gid=GoodsId from tb_GoodsName where GoodsName=@GoodsName'
exec sp_executesql @sql ,N'@gid int output,@GoodsName varchar(50)',@gid output,@GoodsName
exec(@sql)
--改为
select @gid=GoodsId from tb_GoodsName where GoodsName=@GoodsName这里不需要动态拼接执行
上面的错误解决了 下面的错误出来了
else
begin
insert into tb_GoodsName(GoodsName) values(@GoodsName)
select @@identity
insert into tb_Class_Goods(GoodsId,ClassId) values(@@identity,@StoreName)
--下面这条语句取值不成功
select @@identity
---因为上面没有取到值 这条语句中的@@identity对应的值GoodsID值为NULL,不知道为什么会这样呢??
insert into tb_InStore(GoodsID,StoreName,
xiongjing,dijing,pengjing,gaodu,fenzhi,GoodsUnit,GoodsNum,GoodsPrice,shoujia,HandlePeople,ISRe,pinzhi,PrName,isgood,isbareroot)
values(@@identity,@StoreName,@xiongjing,@dijing,@pengjing,@gaodu,@fenzhi,@GoodsUnit,@GoodsNum,
@GoodsPrice,@ShouJia,@HandlePeople,@ISRe,@Pinzhi,@GYid,@IsGood,@IsBareRoot)
end
declare @i1 int,@i2 int
select @i=@@identity
insert into tb_Class_Goods(GoodsId,ClassId) values(@i1,@StoreName);select @i2=@@identity
insert into tb_InStore(GoodsID,StoreName, xiongjing,dijing,pengjing,gaodu,fenzhi,GoodsUnit,GoodsNum,GoodsPrice,shoujia,HandlePeople,ISRe,pinzhi,PrName,isgood,isbareroot)
values(@i2,@StoreName,@xiongjing,@dijing,@pengjing,@gaodu,@fenzhi,@GoodsUnit,@GoodsNum,
@GoodsPrice,@ShouJia,@HandlePeople,@ISRe,@Pinzhi,@GYid,@IsGood,@IsBareRoot)
应改为
exec sp_executesql @sql,N'@gid int output,@goodsname varchar(50)',@gid output,@goodsname
没有自增列,select @@identity 的目的是什么?
我理解的@@identity的作用是返回插入的数据的最后一行的第一列的数据是吧?
也是想取得GoodsId 是我理解错了?
这两个@@identity的值应该是一样的
我发现这个值有时候有 有时候又没有呢
http://msdn.microsoft.com/zh-cn/library/ms187342.aspx