创建存储过程：将非管理员且上月未登录员工状态更新为0

需求概述 #

目标：编写一个存储过程，自动将满足以下两个条件的员工状态更新为'0'：

• 员工为非管理员
• 最后登录时间超过一个月

相关表结构与测试数据 #

先整理完整的表创建语句和测试数据（补充了缺失的关联逻辑和数据）：

-- 清理已有表（避免冲突）
DROP TABLE IF EXISTS EmployeeTest;
DROP TABLE IF EXISTS SecurityTest;
DROP TABLE IF EXISTS LOGINTest;

-- 登录信息表
CREATE TABLE LOGINTest (
    LoginID INT NOT NULL PRIMARY KEY,
    UserName Varchar(50),
    Password Varchar(50),
    LastLogin Date
);

-- 插入登录测试数据
INSERT INTO LOGINTest VALUES 
(1, 'Ovais', 'OV1234', '25-Mar-2018'),
(2, 'Jason', 'JS1234', '17-Jun-2018'),
(3, 'Michael', 'MC1234', '05-Jan-2024'); -- 这条数据最后登录时间超过一个月（假设当前为2024-03）

-- 员工信息表（包含状态字段）
CREATE TABLE EmployeeTest (
    EmployeeID INT NOT NULL PRIMARY KEY,
    LoginID INT FOREIGN KEY REFERENCES LOGINTest(LoginID),
    Status CHAR(1) DEFAULT '1' -- '1'表示正常，'0'表示禁用
);

-- 插入员工测试数据
INSERT INTO EmployeeTest VALUES 
(1, 1, '1'),
(2, 2, '1'),
(3, 3, '1');

-- 权限表（标记是否为管理员）
CREATE TABLE SecurityTest (
    SecurityID INT NOT NULL PRIMARY KEY,
    LoginID INT FOREIGN KEY REFERENCES LOGINTest(LoginID),
    IsAdmin BIT DEFAULT 0 -- 0=非管理员，1=管理员
);

-- 插入权限测试数据
INSERT INTO SecurityTest VALUES 
(1, 1, 1), -- Ovais是管理员
(2, 2, 0), -- Jason是非管理员
(3, 3, 0); -- Michael是非管理员

存储过程实现 #

下面是满足需求的存储过程，每一步都加了注释说明逻辑：

CREATE PROCEDURE UpdateInactiveNonAdminEmployees
AS
BEGIN
    -- 关闭计数消息，让输出更简洁
    SET NOCOUNT ON;

    -- 更新符合条件的员工状态
    UPDATE e
    SET e.Status = '0'
    FROM EmployeeTest e
    -- 关联登录表获取最后登录时间
    JOIN LOGINTest l ON e.LoginID = l.LoginID
    -- 关联权限表判断是否为非管理员
    JOIN SecurityTest s ON e.LoginID = s.LoginID
    WHERE 
        -- 筛选非管理员
        s.IsAdmin = 0
        -- 筛选最后登录时间超过一个月的记录
        AND l.LastLogin < DATEADD(MONTH, -1, GETDATE());

    -- 输出更新的行数，方便验证结果
    PRINT '已更新 ' + CAST(@@ROWCOUNT AS VARCHAR) + ' 条员工记录';
END

逻辑说明 #

1. 表关联：通过LoginID将三张表关联，确保能同时获取员工的权限状态和最后登录时间
2. 时间判断：用DATEADD(MONTH, -1, GETDATE())计算当前日期往前推一个月的节点，只要最后登录时间早于这个节点就符合条件
3. 权限筛选：通过SecurityTest表的IsAdmin字段排除管理员，只处理非管理员员工
4. 结果反馈：用@@ROWCOUNT返回更新的记录数，方便确认操作效果

内容的提问来源于stack exchange，提问作者Doonie Darkoo