nworderswsstoredprocs.sql

来自「wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重」· SQL 代码 · 共 126 行

CREATE PROCEDURE ipGetOrder
   (@OrderID int)
AS
  SET NOCOUNT ON
  SELECT * FROM Orders WHERE OrderID = @OrderID;
  SELECT * FROM [Order Details] WHERE OrderID = @OrderID;

GO

CREATE PROCEDURE ipInsertDetail(
@OrderID int,
@ProductID int,
@UnitPrice money,
@Quantity smallint,
@Discount decimal(5,3) = 0,
@DelDetails bit = 0)

AS
SET NOCOUNT ON
IF @DelDetails = 1
DELETE [Order Details] WHERE OrderID = @OrderID

INSERT [Order Details] VALUES(@OrderID, @ProductID, @UnitPrice, 
@Quantity, @Discount)

IF @@ERROR = 0
   RETURN @OrderID
ELSE
   RETURN -5

GO

CREATE PROCEDURE ipInsertOrder(
@CustomerID varchar(5), 
@EmployeeID int, 
@OrderDate datetime, 
@RequiredDate datetime = NULL, 
@ShippedDate datetime = NULL, 
@ShipVia int, 
@Freight money, 
@ShipName varchar(40), 
@ShipAddress varchar(60), 
@ShipCity varchar(15), 
@ShipRegion varchar(15) = NULL, 
@ShipPostalCode varchar(10) = NULL, 
@ShipCountry varchar(15))

AS
SET NOCOUNT ON 
INSERT Orders (
CustomerID, 
EmployeeID, 
OrderDate, 
RequiredDate, 
ShippedDate, 
ShipVia, 
Freight, 
ShipName, 
ShipAddress, 
ShipCity, 
ShipRegion, 
ShipPostalCode, 
ShipCountry)

VALUES(
@CustomerID, 
@EmployeeID, 
@OrderDate, 
@RequiredDate, 
@ShippedDate, 
@ShipVia, 
@Freight, 
@ShipName, 
@ShipAddress, 
@ShipCity, 
@ShipRegion, 
@ShipPostalCode, 
@ShipCountry)

IF @@ERROR = 0
   RETURN @@IDENTITY
ELSE
   RETURN -5

GO

CREATE PROCEDURE ipUpdateOrder(
@OrderID int,
@CustomerID varchar(5), 
@EmployeeID int, 
@OrderDate datetime, 
@RequiredDate datetime = NULL, 
@ShippedDate datetime = NULL, 
@ShipVia int, 
@Freight money, 
@ShipName varchar(40), 
@ShipAddress varchar(60), 
@ShipCity varchar(15), 
@ShipRegion varchar(15) = NULL, 
@ShipPostalCode varchar(10) = NULL, 
@ShipCountry varchar(15))

AS
SET NOCOUNT ON 
UPDATE Orders SET 
CustomerID = @CustomerID, 
EmployeeID = @EmployeeID, 
OrderDate = @OrderDate, 
RequiredDate = @RequiredDate, 
ShippedDate = @ShippedDate, 
ShipVia = @ShipVia, 
Freight = @Freight, 
ShipName = @ShipName, 
ShipAddress = @ShipAddress, 
ShipCity = @ShipCity, 
ShipRegion = @ShipRegion, 
ShipPostalCode = @ShipPostalCode, 
ShipCountry = @ShipCountry 
WHERE OrderID = @OrderID
IF @@ERROR = 0
   RETURN @OrderID
ELSE
   RETURN -5

GO