怎么把存储过程改回SQL句子
基于Access数据库,实现购物功能的程序.微软IBuySpy(购物程序示例)中的一个存储过程和我要的功能可接近,怎么仿之写出SQL句子.
Orders是订单表,OrdersDetail是明细表,
ShoppingCart 是购物车,Products 是商品表
CREATE Procedure OrdersAdd
(
@CustomerID int,
@CartID nvarchar(50),
@OrderDate datetime,
@ShipDate datetime,
@OrderID int OUTPUT
)
AS
BEGIN TRAN AddOrder
/* Create the Order header */
INSERT INTO Orders
(
CustomerID,
OrderDate,
ShipDate
)
VALUES
(
@CustomerID,
@OrderDate,
@ShipDate
)
SELECT
@OrderID = @@Identity
/* Copy items from given shopping cart to OrdersDetail table for given OrderID*/
INSERT INTO OrderDetails
(
OrderID,
ProductID,
Quantity,
UnitCost
)
SELECT
@OrderID,
ShoppingCart.ProductID,
Quantity,
Products.UnitCost
FROM
ShoppingCart
INNER JOIN Products ON ShoppingCart.ProductID = Products.ProductID
WHERE
CartID = @CartID
/* Removal of items from user's shopping cart will happen on the business layer*/
EXEC ShoppingCartEmpty @CartId
COMMIT TRAN AddOrder
GO