下面是我写的一个存储过程,因为第一次写这东西,不知道是不是语句写错,总是报错,大家帮看看:
CREATE PROCEDURE dbo.create_orderdetail
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
@goodsid char(10),
@goodsname varchar(250),
@goodsprice varchar(20),
@goodsnum varchar(10),
@orderid varchar(22),
@returnkey int output,
@userid varchar(20)
AS
/* SET NOCOUNT ON */
begin transaction
select goodsstocknum from shop_goods(updlock) where goodsid =@goodsid
if (goodsstocknum<>-1 and cast(@goodsid as int)<=goodsstocknum)
begin
if (cast(@goodsnum as int)<goodsstocknum) /* 如果购买的数量小于库存,则更新库存和预销售数*/
begin
update shop_goods set goodsstocknum=goodsstocknum-cast(@goodsnum as int),goodsordersellnum=goodsordersellnum+cast(@goodsnum
as int) where goodsid=@goodsid
--插入详细列表
insert into orderdetail (goodsid,goodsname,goodsprice,goodsnum,orderid,userid) select @goodsid,@goodsname
,cast(@goodsprice as money),cast(@goodsnum as int),@orderid,@userid
end
else
begin
--购买数量等于库存更新商品列表并设置下线
update shop_goods set goodsstocknum=goodsstocknum-cast(@goodsnum as int),goodsordersellnum=goodsordersellnum+cast(@goodsnum as int),visible='1'
where goodsid=@goodsid
--插入详细列表
insert into orderdetail (goodsid,goodsname,goodsprice,goodsnum,orderid,userid) select @goodsid,@goodsname
,cast(@goodsprice as money),cast(@goodsnum as int),@orderid,@userid
end
commit transaction /*事务结束*/
end
else
begin
set @returnkey=0 /*有商品的购买数量大于库存,做记号*/
rollback transaction
end
RETURN
CREATE PROCEDURE dbo.create_orderdetail
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
@goodsid char(10),
@goodsname varchar(250),
@goodsprice varchar(20),
@goodsnum varchar(10),
@orderid varchar(22),
@returnkey int output,
@userid varchar(20)
AS
/* SET NOCOUNT ON */
begin transaction
select goodsstocknum from shop_goods(updlock) where goodsid =@goodsid
if (goodsstocknum<>-1 and cast(@goodsid as int)<=goodsstocknum)
begin
if (cast(@goodsnum as int)<goodsstocknum) /* 如果购买的数量小于库存,则更新库存和预销售数*/
begin
update shop_goods set goodsstocknum=goodsstocknum-cast(@goodsnum as int),goodsordersellnum=goodsordersellnum+cast(@goodsnum
as int) where goodsid=@goodsid
--插入详细列表
insert into orderdetail (goodsid,goodsname,goodsprice,goodsnum,orderid,userid) select @goodsid,@goodsname
,cast(@goodsprice as money),cast(@goodsnum as int),@orderid,@userid
end
else
begin
--购买数量等于库存更新商品列表并设置下线
update shop_goods set goodsstocknum=goodsstocknum-cast(@goodsnum as int),goodsordersellnum=goodsordersellnum+cast(@goodsnum as int),visible='1'
where goodsid=@goodsid
--插入详细列表
insert into orderdetail (goodsid,goodsname,goodsprice,goodsnum,orderid,userid) select @goodsid,@goodsname
,cast(@goodsprice as money),cast(@goodsnum as int),@orderid,@userid
end
commit transaction /*事务结束*/
end
else
begin
set @returnkey=0 /*有商品的购买数量大于库存,做记号*/
rollback transaction
end
RETURN
解决方案 »
- gridview的数据绑定与datalist不一样?
- 哈哈,一个入门级的Linq,搞着玩的,放点分
- word调用
- 请教一个关于asp.net ajax(atlas)的简单问题?急,在线等
- 怎么用脚本控制服务器端控件(在线等)
- ASP.NET三层架构
- 在线等待:我用SQL建了一存储过程,但要求用户传两参数即可执行,不知在ASP。NET下用C#如何写存储过程调用该参数?
- 急,DataGrid中DropDownList的动态绑定,大家帮忙看看
- foreach中怎么查看当前遍历到第几个
- Asp.net 跳转到指定的页面??怎么把js的返回值引用到链接中??
- 求救:有关IE Web Control的Treeview问题!
- 怎么取一个字符串最右边的两位?
select goodsstocknum from shop_goods with (nolock) where goodsid =@goodsid
values(@goodsid,@goodsname,cast(@goodsprice as money),cast(@goodsnum as int),@orderid,@userid)
现在是报错列名goodsstocknum无效,应该与您说的无关吧
列名不是变量,哪能直接计算!
修改了一下没有什么错误了
CREATE PROCEDURE dbo.create_orderdetail
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
@goodsid char(10),
@goodsname varchar(250),
@goodsprice varchar(20),
@goodsnum varchar(10),
@orderid varchar(22),
@returnkey int output,
@userid varchar(20)
ASdeclare @goodsstocknum int /* SET NOCOUNT ON */
begin transaction
set @goodsstocknum = (select goodsstocknum from shop_goods(updlock) where goodsid =@goodsid)
if (@goodsstocknum<>-1 and cast(@goodsid as int)<=@goodsstocknum)
begin
if (cast(@goodsnum as int)<@goodsstocknum) /* 如果购买的数量小于库存,则更新库存和预销售数*/
begin
update shop_goods set goodsstocknum=goodsstocknum-cast(@goodsnum as int),goodsordersellnum=goodsordersellnum+cast(@goodsnum
as int) where goodsid=@goodsid
--插入详细列表
insert into orderdetail (goodsid,goodsname,goodsprice,goodsnum,orderid,userid) select @goodsid,@goodsname
,cast(@goodsprice as money),cast(@goodsnum as int),@orderid,@userid
end
else
begin
--购买数量等于库存更新商品列表并设置下线
update shop_goods set goodsstocknum=goodsstocknum-cast(@goodsnum as int),goodsordersellnum=goodsordersellnum+cast(@goodsnum as int),visible='1'
where goodsid=@goodsid
--插入详细列表
insert into orderdetail (goodsid,goodsname,goodsprice,goodsnum,orderid,userid) select @goodsid,@goodsname
,cast(@goodsprice as money),cast(@goodsnum as int),@orderid,@userid
end
commit transaction /*事务结束*/
end
else
begin
set @returnkey=0 /*有商品的购买数量大于库存,做记号*/
rollback transaction
end
RETURN
if (goodsstocknum<>-1 and cast(@goodsid as int)<=goodsstocknum)你上面第一句还不会有错,但第二句那里goodsstocknum不是申明的变量,肯定不能这样操作啦。改成这样。declare @goodsstocknum int
select @goodsstocknum from shop_goods(updlock) where goodsid =@goodsid
if (@goodsstocknum<>-1 and cast(@goodsid as int)<=@goodsstocknum)后面的有比较goodsstocknum的你也按上面的方法去改。