/* Sql scripts for DAPrototype*/
USE Northwind
GO
/* Used in DAPrototype*/
CREATE PROCEDURE SPSelCustomers
(
@CustomerID varchar(5)
)
AS
SELECT
C.CustomerID,C.CompanyName,C.Address,C.City
FROM Customers C
WHERE C.CustomerID LIKE @CustomerID + '%'
GO
/* Used in DAPrototype*/
CREATE PROCEDURE SPSelOrders
(
@CustomerID nchar(5)
)
AS
SELECT O.OrderID,O.CustomerID, O.OrderDate
FROM
Orders O
WHERE O.CustomerID = @CustomerID
ORDER BY O.OrderDate DESC
GO
/* Used in DAPrototype*/
CREATE PROCEDURE SPSelOrderDetail
(
@OrderID int
)
AS
SELECT OD.ProductID , OD.UnitPrice,OD.Quantity, P.ProductName
FROM [Order Details] OD INNER JOIN Products P
ON OD.ProductID = P.ProductID
WHERE OD.ORDERID =@OrderID
GO
/* Used in DAPrototype*/
CREATE PROCEDURE SPUpOrderDetail
(
@OrderID int,
@ProductID int,
@Quantity int
)
AS
UPDATE [Order Details]
SET Quantity =@Quantity
WHERE ProductID=@ProductID AND OrderID=@OrderID
GO
/* Used in DAPrototype */
CREATE PROCEDURE SPDelOrders
(
@OrderID int
)
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
DELETE [Order Details] WHERE OrderID = @OrderID
DELETE Orders WHERE OrderID = @OrderID
IF @@error > 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
GO
/* Used in DAPrototype*/
CREATE PROCEDURE SPDelOrdersDemo
(
@OrderID int
)
AS
DELETE Orders WHERE OrderID = @OrderID
GO
/* Used in DAPrototype*/
CREATE PROCEDURE SPDelODDemo
(
@OrderID int
)
AS
DELETE [Order Details] WHERE OrderID = @OrderID
GO
