if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[Orders_Insert]
GO
------------------------------------
-- 功能描述：增加一条记录 
-- 所属表格：Orders[ ]
-- 作者名称：
-- 创建日期：
-- 修改日期：
 /*
 -- 测试代码 --
 -- 参数设置 
DECLARE @OrderID int -- 订单ID
    SET @OrderID = 
DECLARE @CustomerID nchar -- 客户ID
    SET @CustomerID = 
DECLARE @EmployeeID int -- 雇员ID
    SET @EmployeeID = 
DECLARE @OrderDate datetime -- 订购日期
    SET @OrderDate = 
DECLARE @RequiredDate datetime -- 发货日期
    SET @RequiredDate = 
DECLARE @ShippedDate datetime -- 到货日期
    SET @ShippedDate = 
DECLARE @ShipVia int -- 运货商ID
    SET @ShipVia = 
DECLARE @Freight money -- 运货费
    SET @Freight = 0
DECLARE @ShipName nvarchar(40) -- 运货商
    SET @ShipName =  ' '
DECLARE @ShipAddress nvarchar(60) -- 货主地址
    SET @ShipAddress =  ' '
DECLARE @ShipCity nvarchar(15) -- 货主城市
    SET @ShipCity =  ' '
DECLARE @ShipRegion nvarchar(15) -- 货主国家
    SET @ShipRegion =  ' '
DECLARE @ShipPostalCode nvarchar(10) -- 货主邮政编码
    SET @ShipPostalCode =  ' '
DECLARE @ShipCountry nvarchar(15) -- 货主国家
    SET @ShipCountry =  ' '
 -- 调用存储过程 
 exec [Orders_Insert] @OrderID,@CustomerID,@EmployeeID,@OrderDate,@RequiredDate,@ShippedDate,@ShipVia,@Freight,@ShipName,@ShipAddress,@ShipCity,@ShipRegion,@ShipPostalCode,@ShipCountry
 */
------------------------------------
CREATE PROCEDURE [Orders_Insert] (
@OrderID         int = NULL,            -- 订单ID
@CustomerID      nchar = NULL,          -- 客户ID
@EmployeeID      int = NULL,            -- 雇员ID
@OrderDate       datetime = NULL,       -- 订购日期
@RequiredDate    datetime = NULL,       -- 发货日期
@ShippedDate     datetime = NULL,       -- 到货日期
@ShipVia         int = NULL,            -- 运货商ID
@Freight         money,                 -- 运货费
@ShipName        nvarchar(40) = NULL,   -- 运货商
@ShipAddress     nvarchar(60) = NULL,   -- 货主地址
@ShipCity        nvarchar(15) = NULL,   -- 货主城市
@ShipRegion      nvarchar(15) = NULL,   -- 货主国家
@ShipPostalCode  nvarchar(10) = NULL,   -- 货主邮政编码
@ShipCountry     nvarchar(15) = NULL    -- 货主国家

)
AS
INSERT INTO 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	
)
SELECT @ID = SCOPE_IDENTITY() -- 返回新加记录的唯一标识
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders_Edit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Orders_Edit]
GO
------------------------------------
-- 功能描述：修改一条记录 
-- 所属表格：Orders[]
-- 作者名称：
-- 创建日期：
-- 修改日期：
 /* -- 测试代码 --
 -- 参数设置 
DECLARE @OrderID int -- 订单ID
    SET @OrderID = 
DECLARE @CustomerID nchar -- 客户ID
    SET @CustomerID = 
DECLARE @EmployeeID int -- 雇员ID
    SET @EmployeeID = 
DECLARE @OrderDate datetime -- 订购日期
    SET @OrderDate = 
DECLARE @RequiredDate datetime -- 发货日期
    SET @RequiredDate = 
DECLARE @ShippedDate datetime -- 到货日期
    SET @ShippedDate = 
DECLARE @ShipVia int -- 运货商ID
    SET @ShipVia = 
DECLARE @Freight money -- 运货费
    SET @Freight = 0
DECLARE @ShipName nvarchar(40) -- 运货商
    SET @ShipName =  ' '
DECLARE @ShipAddress nvarchar(60) -- 货主地址
    SET @ShipAddress =  ' '
DECLARE @ShipCity nvarchar(15) -- 货主城市
    SET @ShipCity =  ' '
DECLARE @ShipRegion nvarchar(15) -- 货主国家
    SET @ShipRegion =  ' '
DECLARE @ShipPostalCode nvarchar(10) -- 货主邮政编码
    SET @ShipPostalCode =  ' '
DECLARE @ShipCountry nvarchar(15) -- 货主国家
    SET @ShipCountry =  ' '
 -- 调用存储过程 
 exec [Orders_Edit] @OrderID,@CustomerID,@EmployeeID,@OrderDate,@RequiredDate,@ShippedDate,@ShipVia,@Freight,@ShipName,@ShipAddress,@ShipCity,@ShipRegion,@ShipPostalCode,@ShipCountry 
 */
------------------------------------
CREATE PROCEDURE [Orders_Edit]
@OrderID         int = NULL,            -- 订单ID
@CustomerID      nchar = NULL,          -- 客户ID
@EmployeeID      int = NULL,            -- 雇员ID
@OrderDate       datetime = NULL,       -- 订购日期
@RequiredDate    datetime = NULL,       -- 发货日期
@ShippedDate     datetime = NULL,       -- 到货日期
@ShipVia         int = NULL,            -- 运货商ID
@Freight         money,                 -- 运货费
@ShipName        nvarchar(40) = NULL,   -- 运货商
@ShipAddress     nvarchar(60) = NULL,   -- 货主地址
@ShipCity        nvarchar(15) = NULL,   -- 货主城市
@ShipRegion      nvarchar(15) = NULL,   -- 货主国家
@ShipPostalCode  nvarchar(10) = NULL,   -- 货主邮政编码
@ShipCountry     nvarchar(15) = NULL    -- 货主国家

AS 
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
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Orders_Delete]
GO
------------------------------------
-- 功能描述：删除一条记录 
-- 作者名称：
-- 创建日期：
-- 修改日期：
 /*
 -- 测试代码 --
 -- 参数设置 
 -- 调用存储过程 
DECLARE @ID int
    set @ID=0
 exec [Orders_Delete] @ID
 */
------------------------------------
CREATE PROCEDURE [Orders_Delete]
	@OrderID int --待删除的主键
AS 
   
	    DELETE FROM [Orders]	    WHERE OrderID = @OrderID 
     
     
GO   
     


