if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[Products_Insert]
GO
------------------------------------
-- 功能描述：增加一条记录 
-- 所属表格：Products[ ]
-- 作者名称：
-- 创建日期：
-- 修改日期：
 /*
 -- 测试代码 --
 -- 参数设置 
DECLARE @ProductID int -- 产品ID
    SET @ProductID = 
DECLARE @ProductName nvarchar(40) -- 产品名称
    SET @ProductName =  ' '
DECLARE @SupplierID int -- 供应商ID
    SET @SupplierID = 
DECLARE @CategoryID int -- 类别ID
    SET @CategoryID = 
DECLARE @QuantityPerUnit nvarchar(20) -- 数量单位
    SET @QuantityPerUnit =  ' '
DECLARE @UnitPrice money -- 单价
    SET @UnitPrice = 0
DECLARE @UnitsInStock smallint -- 库存量
    SET @UnitsInStock = 0
DECLARE @UnitsOnOrder smallint -- 订购量
    SET @UnitsOnOrder = 0
DECLARE @ReorderLevel smallint -- 再订购量
    SET @ReorderLevel = 0
DECLARE @Discontinued bit -- 中止
    SET @Discontinued = 0
 -- 调用存储过程 
 exec [Products_Insert] @ProductID,@ProductName,@SupplierID,@CategoryID,@QuantityPerUnit,@UnitPrice,@UnitsInStock,@UnitsOnOrder,@ReorderLevel,@Discontinued
 */
------------------------------------
CREATE PROCEDURE [Products_Insert] (
@ProductID        int = NULL,            -- 产品ID
@ProductName      nvarchar(40) = NULL,   -- 产品名称
@SupplierID       int = NULL,            -- 供应商ID
@CategoryID       int = NULL,            -- 类别ID
@QuantityPerUnit  nvarchar(20) = NULL,   -- 数量单位
@UnitPrice        money,                 -- 单价
@UnitsInStock     smallint,              -- 库存量
@UnitsOnOrder     smallint,              -- 订购量
@ReorderLevel     smallint,              -- 再订购量
@Discontinued     bit = NULL             -- 中止

)
AS
INSERT INTO Products(
	[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]	 
)VALUES( 
	@ProductName,@SupplierID,@CategoryID,@QuantityPerUnit,@UnitPrice,@UnitsInStock,@UnitsOnOrder,@ReorderLevel,@Discontinued	
)
SELECT @ID = SCOPE_IDENTITY() -- 返回新加记录的唯一标识
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products_Edit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Products_Edit]
GO
------------------------------------
-- 功能描述：修改一条记录 
-- 所属表格：Products[]
-- 作者名称：
-- 创建日期：
-- 修改日期：
 /* -- 测试代码 --
 -- 参数设置 
DECLARE @ProductID int -- 产品ID
    SET @ProductID = 
DECLARE @ProductName nvarchar(40) -- 产品名称
    SET @ProductName =  ' '
DECLARE @SupplierID int -- 供应商ID
    SET @SupplierID = 
DECLARE @CategoryID int -- 类别ID
    SET @CategoryID = 
DECLARE @QuantityPerUnit nvarchar(20) -- 数量单位
    SET @QuantityPerUnit =  ' '
DECLARE @UnitPrice money -- 单价
    SET @UnitPrice = 0
DECLARE @UnitsInStock smallint -- 库存量
    SET @UnitsInStock = 0
DECLARE @UnitsOnOrder smallint -- 订购量
    SET @UnitsOnOrder = 0
DECLARE @ReorderLevel smallint -- 再订购量
    SET @ReorderLevel = 0
DECLARE @Discontinued bit -- 中止
    SET @Discontinued = 0
 -- 调用存储过程 
 exec [Products_Edit] @ProductID,@ProductName,@SupplierID,@CategoryID,@QuantityPerUnit,@UnitPrice,@UnitsInStock,@UnitsOnOrder,@ReorderLevel,@Discontinued 
 */
------------------------------------
CREATE PROCEDURE [Products_Edit]
@ProductID        int = NULL,            -- 产品ID
@ProductName      nvarchar(40) = NULL,   -- 产品名称
@SupplierID       int = NULL,            -- 供应商ID
@CategoryID       int = NULL,            -- 类别ID
@QuantityPerUnit  nvarchar(20) = NULL,   -- 数量单位
@UnitPrice        money,                 -- 单价
@UnitsInStock     smallint,              -- 库存量
@UnitsOnOrder     smallint,              -- 订购量
@ReorderLevel     smallint,              -- 再订购量
@Discontinued     bit = NULL             -- 中止

AS 
UPDATE Products SET 
	 [ProductName] = @ProductName, [SupplierID] = @SupplierID, [CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit, [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, [UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel, [Discontinued] = @Discontinued
WHERE 
	 [ProductID] = @ProductID
GO


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


