一個項目要實現和Windows日誌互動,需要在Northwind資料庫裡面產生如下預存程序的指令碼!

/* 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