写三个人事管理系统的存储过程

在编写人事管理系统中的存储过程时，通常会涉及到员工信息的增删改查操作。以下是三个常见的存储过程示例：

1. **插入新员工信息** (`InsertEmployee`):

CREATE PROCEDURE InsertEmployee (
    @ID INT,
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @JobTitle VARCHAR(100),
    @HireDate DATE
)
AS
BEGIN
    INSERT INTO Employees (ID, FirstName, LastName, JobTitle, HireDate)
    VALUES (@ID, @FirstName, @LastName, @JobTitle, @HireDate);
END;

这个过程用于向`Employees`表中添加新的员工记录。

2. **更新员工信息** (`UpdateEmployee`):

CREATE PROCEDURE UpdateEmployee (
    @ID INT,
    @NewFirstName VARCHAR(50) = NULL,
    @NewLastName VARCHAR(50) = NULL,
    @NewJobTitle VARCHAR(100) = NULL,
    @NewHireDate DATE = NULL
)
AS
BEGIN
    IF (@NewFirstName IS NOT NULL OR @NewLastName IS NOT NULL OR ...)
        UPDATE Employees
        SET FirstName = COALESCE(@NewFirstName, FirstName),
            LastName = COALESCE(@NewLastName, LastName),
            JobTitle = COALESCE(@NewJobTitle, JobTitle),
            HireDate = CASE WHEN @NewHireDate IS NOT NULL THEN @NewHireDate ELSE HireDate END
        WHERE ID = @ID;
END;

这个过程允许管理员更新指定员工的部分或全部信息，字段值为空则表示不变。

3. **查询员工列表** (`GetEmployeesByDepartment`):

CREATE PROCEDURE GetEmployeesByDepartment (@DepartmentID INT)
AS
BEGIN
    SELECT * FROM Employees
    WHERE DepartmentID = @DepartmentID;
END;

这个过程根据部门ID返回所有属于该部门的员工信息。