问题描述

sql 如何在建立一个存储过程时更新两张表的数据？

create proc proc2
(
@OrderID int,
@BookID varchar(20),--更新 orderInfo quantity；更新 orderSheet payment
@BookStock int
)
as
update orderSheet
set payment = b.newpay
FROM orderInfo,orderSheet,(select (payment + price * @BookStock) newpay
FROM orderSheet,book,(update orderInfo
SET quantity = a.qq
FROM (select (o1.quantity + @BookStock) qq
from orderInfo o1
WHERE o1.orderid = @OrderID
AND o1.bookid = @BookID) a) aa
WHERE orderSheet.oid = aa.orderid
AND aa.bookid = book.bid) b
where orderInfo.orderid = @OrderID
AND orderInfo.bookid = @BookID
AND orderSheet.oid = orderInfo.orderid

求问：这样错在哪里？正确的又应该怎样做？

解决方案

你这个嵌套太多了吧，为什么不分步骤来做呢

解决方案二：

create proc proc2
(
@OrderID int,
@BookID varchar(20),--更新 orderInfo quantity；更新 orderSheet payment
@BookStock int
)
as
begin

update orderInfo
SET quantity =(quantity + @BookStock)
WHERE orderid = @OrderID
AND bookid = @BookID

update orderSheet
set payment = orderSheet.payment+orderInfo.price*orderInfo.quantity
FROM orderInfo
where orderInfo.orderid = @OrderID
AND orderInfo.bookid = @BookID
AND orderSheet.oid = orderInfo.orderid

end
go