if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employees_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[Employees_Insert]
GO
------------------------------------
-- 功能描述：增加一条记录 
-- 所属表格：Employees[ ]
-- 作者名称：
-- 创建日期：
-- 修改日期：
 /*
 -- 测试代码 --
 -- 参数设置 
DECLARE @EmployeeID int -- 雇员ID
    SET @EmployeeID = 
DECLARE @LastName nvarchar(20) -- 姓氏
    SET @LastName =  ' '
DECLARE @FirstName nvarchar(10) -- 名称
    SET @FirstName =  ' '
DECLARE @Title nvarchar(30) -- 职位
    SET @Title =  ' '
DECLARE @TitleOfCourtesy nvarchar(25) -- 尊称
    SET @TitleOfCourtesy =  ' '
DECLARE @BirthDate datetime -- 出生日期
    SET @BirthDate = 
DECLARE @HireDate datetime -- 雇用日期
    SET @HireDate = 
DECLARE @Address nvarchar(60) -- 地址
    SET @Address =  ' '
DECLARE @City nvarchar(15) -- 城市
    SET @City =  ' '
DECLARE @Region nvarchar(15) -- 地区
    SET @Region =  ' '
DECLARE @PostalCode nvarchar(10) -- 邮政编码
    SET @PostalCode =  ' '
DECLARE @Country nvarchar(15) -- 国家
    SET @Country =  ' '
DECLARE @HomePhone nvarchar(24) -- 家庭电话
    SET @HomePhone =  ' '
DECLARE @Extension nvarchar(4) -- 电话分机
    SET @Extension =  ' '
DECLARE @Photo image -- 照片
    SET @Photo = 
DECLARE @Notes ntext -- 备注
    SET @Notes =  ' '
DECLARE @ReportsTo int -- 上级雇员
    SET @ReportsTo = 
DECLARE @PhotoPath nvarchar(255) -- 图片路径
    SET @PhotoPath =  ' '
 -- 调用存储过程 
 exec [Employees_Insert] @EmployeeID,@LastName,@FirstName,@Title,@TitleOfCourtesy,@BirthDate,@HireDate,@Address,@City,@Region,@PostalCode,@Country,@HomePhone,@Extension,@Photo,@Notes,@ReportsTo,@PhotoPath
 */
------------------------------------
CREATE PROCEDURE [Employees_Insert] (
@EmployeeID       int = NULL,            -- 雇员ID
@LastName         nvarchar(20) = NULL,   -- 姓氏
@FirstName        nvarchar(10) = NULL,   -- 名称
@Title            nvarchar(30) = NULL,   -- 职位
@TitleOfCourtesy  nvarchar(25) = NULL,   -- 尊称
@BirthDate        datetime = NULL,       -- 出生日期
@HireDate         datetime = NULL,       -- 雇用日期
@Address          nvarchar(60) = NULL,   -- 地址
@City             nvarchar(15) = NULL,   -- 城市
@Region           nvarchar(15) = NULL,   -- 地区
@PostalCode       nvarchar(10) = NULL,   -- 邮政编码
@Country          nvarchar(15) = NULL,   -- 国家
@HomePhone        nvarchar(24) = NULL,   -- 家庭电话
@Extension        nvarchar(4) = NULL,    -- 电话分机
@Photo            image = NULL,          -- 照片
@Notes            ntext = NULL,          -- 备注
@ReportsTo        int = NULL,            -- 上级雇员
@PhotoPath        nvarchar(255) = NULL   -- 图片路径

)
AS
INSERT INTO Employees(
	[LastName],[FirstName],[Title],[TitleOfCourtesy],[BirthDate],[HireDate],[Address],[City],[Region],[PostalCode],[Country],[HomePhone],[Extension],[Photo],[Notes],[ReportsTo],[PhotoPath]	 
)VALUES( 
	@LastName,@FirstName,@Title,@TitleOfCourtesy,@BirthDate,@HireDate,@Address,@City,@Region,@PostalCode,@Country,@HomePhone,@Extension,@Photo,@Notes,@ReportsTo,@PhotoPath	
)
SELECT @ID = SCOPE_IDENTITY() -- 返回新加记录的唯一标识
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employees_Edit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Employees_Edit]
GO
------------------------------------
-- 功能描述：修改一条记录 
-- 所属表格：Employees[]
-- 作者名称：
-- 创建日期：
-- 修改日期：
 /* -- 测试代码 --
 -- 参数设置 
DECLARE @EmployeeID int -- 雇员ID
    SET @EmployeeID = 
DECLARE @LastName nvarchar(20) -- 姓氏
    SET @LastName =  ' '
DECLARE @FirstName nvarchar(10) -- 名称
    SET @FirstName =  ' '
DECLARE @Title nvarchar(30) -- 职位
    SET @Title =  ' '
DECLARE @TitleOfCourtesy nvarchar(25) -- 尊称
    SET @TitleOfCourtesy =  ' '
DECLARE @BirthDate datetime -- 出生日期
    SET @BirthDate = 
DECLARE @HireDate datetime -- 雇用日期
    SET @HireDate = 
DECLARE @Address nvarchar(60) -- 地址
    SET @Address =  ' '
DECLARE @City nvarchar(15) -- 城市
    SET @City =  ' '
DECLARE @Region nvarchar(15) -- 地区
    SET @Region =  ' '
DECLARE @PostalCode nvarchar(10) -- 邮政编码
    SET @PostalCode =  ' '
DECLARE @Country nvarchar(15) -- 国家
    SET @Country =  ' '
DECLARE @HomePhone nvarchar(24) -- 家庭电话
    SET @HomePhone =  ' '
DECLARE @Extension nvarchar(4) -- 电话分机
    SET @Extension =  ' '
DECLARE @Photo image -- 照片
    SET @Photo = 
DECLARE @Notes ntext -- 备注
    SET @Notes =  ' '
DECLARE @ReportsTo int -- 上级雇员
    SET @ReportsTo = 
DECLARE @PhotoPath nvarchar(255) -- 图片路径
    SET @PhotoPath =  ' '
 -- 调用存储过程 
 exec [Employees_Edit] @EmployeeID,@LastName,@FirstName,@Title,@TitleOfCourtesy,@BirthDate,@HireDate,@Address,@City,@Region,@PostalCode,@Country,@HomePhone,@Extension,@Photo,@Notes,@ReportsTo,@PhotoPath 
 */
------------------------------------
CREATE PROCEDURE [Employees_Edit]
@EmployeeID       int = NULL,            -- 雇员ID
@LastName         nvarchar(20) = NULL,   -- 姓氏
@FirstName        nvarchar(10) = NULL,   -- 名称
@Title            nvarchar(30) = NULL,   -- 职位
@TitleOfCourtesy  nvarchar(25) = NULL,   -- 尊称
@BirthDate        datetime = NULL,       -- 出生日期
@HireDate         datetime = NULL,       -- 雇用日期
@Address          nvarchar(60) = NULL,   -- 地址
@City             nvarchar(15) = NULL,   -- 城市
@Region           nvarchar(15) = NULL,   -- 地区
@PostalCode       nvarchar(10) = NULL,   -- 邮政编码
@Country          nvarchar(15) = NULL,   -- 国家
@HomePhone        nvarchar(24) = NULL,   -- 家庭电话
@Extension        nvarchar(4) = NULL,    -- 电话分机
@Photo            image = NULL,          -- 照片
@Notes            ntext = NULL,          -- 备注
@ReportsTo        int = NULL,            -- 上级雇员
@PhotoPath        nvarchar(255) = NULL   -- 图片路径

AS 
UPDATE Employees SET 
	 [LastName] = @LastName, [FirstName] = @FirstName, [Title] = @Title, [TitleOfCourtesy] = @TitleOfCourtesy, [BirthDate] = @BirthDate, [HireDate] = @HireDate, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [HomePhone] = @HomePhone, [Extension] = @Extension, [Photo] = @Photo, [Notes] = @Notes, [ReportsTo] = @ReportsTo, [PhotoPath] = @PhotoPath
WHERE 
	 [EmployeeID] = @EmployeeID
GO


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


