如何创建存储过程，基于用户输入从三张关联表中检索数据

没问题，我来帮你搞定这个存储过程的需求！先理清楚三张表的完整结构（你提供的SUBJECTS表定义有点截断，我按合理的业务逻辑补全了字段），然后针对不同检索场景给出实用的存储过程方案：

先确认完整表结构 #

首先补全SUBJECTS表的合理定义（如果你的实际表有其他字段，直接替换即可）：

CREATE TABLE DEPARTMENTS ( 
    D_ID INT PRIMARY KEY identity(1, 1), 
    department_name VARCHAR(50) NOT NULL 
);

CREATE TABLE SEMESTER ( 
    D_ID INT FOREIGN KEY REFERENCES departments(D_ID), 
    sem_id INT PRIMARY KEY identity(1, 1), 
    semester INT CHECK (semester BETWEEN 1 AND 8) NOT NULL 
);

-- 补全SUBJECTS表结构
CREATE TABLE SUBJECTS ( 
    D_ID INT FOREIGN KEY REFERENCES departments(D_ID), 
    sem_id INT FOREIGN KEY REFERENCES semester(sem_id), 
    sub_id INT PRIMARY KEY identity(1, 1), 
    subject_name VARCHAR(100) NOT NULL,
    subject_code VARCHAR(20) NOT NULL -- 可选字段，根据实际需求调整
);

1. 常用场景：按部门+学期检索科目 #

这是最基础的需求，支持按部门ID/名称、学期数筛选，返回关联的完整信息：

CREATE PROCEDURE GetSubjectsByDeptAndSemester
    @DepartmentID INT = NULL, -- 可选：按部门ID筛选
    @DepartmentName VARCHAR(50) = NULL, -- 可选：按部门名称筛选
    @SemesterNumber INT = NULL -- 可选：按学期数筛选
AS
BEGIN
    SET NOCOUNT ON; -- 提升性能，避免返回额外元数据

    SELECT 
        d.D_ID,
        d.department_name,
        s.sem_id,
        s.semester,
        sub.sub_id,
        sub.subject_name,
        sub.subject_code
    FROM DEPARTMENTS d
    INNER JOIN SEMESTER s ON d.D_ID = s.D_ID
    INNER JOIN SUBJECTS sub ON s.sem_id = sub.sem_id AND d.D_ID = sub.D_ID
    WHERE 
        -- 灵活处理筛选条件：没传参数就不限制
        (@DepartmentID IS NULL OR d.D_ID = @DepartmentID)
        AND (@DepartmentName IS NULL OR d.department_name = @DepartmentName)
        AND (@SemesterNumber IS NULL OR s.semester = @SemesterNumber);
END

调用示例：

• 获取ID为1的部门第3学期的所有科目：
  EXEC GetSubjectsByDeptAndSemester @DepartmentID = 1, @SemesterNumber = 3;
• 获取所有部门第5学期的科目：
  EXEC GetSubjectsByDeptAndSemester @SemesterNumber = 5;
• 获取“电子工程系”的所有科目（不限制学期）：
  EXEC GetSubjectsByDeptAndSemester @DepartmentName = '电子工程系';

2. 进阶场景：查看某部门的所有学期及对应科目 #

如果需要查看某个部门下所有学期的科目配置情况（包括暂无科目的学期），可以用这个存储过程：

CREATE PROCEDURE GetAllDeptSemestersAndSubjects
    @DepartmentID INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        s.semester,
        sub.sub_id,
        sub.subject_name,
        sub.subject_code
    FROM DEPARTMENTS d
    INNER JOIN SEMESTER s ON d.D_ID = s.D_ID
    LEFT JOIN SUBJECTS sub ON s.sem_id = sub.sem_id AND d.D_ID = sub.D_ID
    WHERE d.D_ID = @DepartmentID
    ORDER BY s.semester, sub.subject_name;
END

说明：

用LEFT JOIN确保即使某个学期还没配置科目，也会显示该学期的记录（科目字段为NULL），方便排查未配置的学期。

调用示例：

EXEC GetAllDeptSemestersAndSubjects @DepartmentID = 2;

3. 灵活场景：支持模糊搜索的通用查询 #

如果需要按科目名称模糊搜索，或者组合多条件查询，可以用这个扩展版：

CREATE PROCEDURE SearchSubjects
    @DepartmentID INT = NULL,
    @SemesterNumber INT = NULL,
    @SubjectNameKeyword VARCHAR(100) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        d.department_name,
        s.semester,
        sub.subject_name,
        sub.subject_code
    FROM DEPARTMENTS d
    INNER JOIN SEMESTER s ON d.D_ID = s.D_ID
    INNER JOIN SUBJECTS sub ON s.sem_id = sub.sem_id AND d.D_ID = sub.D_ID
    WHERE 
        (@DepartmentID IS NULL OR d.D_ID = @DepartmentID)
        AND (@SemesterNumber IS NULL OR s.semester = @SemesterNumber)
        AND (@SubjectNameKeyword IS NULL OR sub.subject_name LIKE '%' + @SubjectNameKeyword + '%');
END

调用示例：

搜索所有包含“数据库”关键词的科目：
EXEC SearchSubjects @SubjectNameKeyword = '数据库';

额外注意事项 #

• 如果你的SUBJECTS表有其他字段（比如学分、课时），直接在SELECT语句中添加对应字段即可。
• SET NOCOUNT ON是SQL Server存储过程的最佳实践，避免返回额外的“影响行数”信息，提升应用程序处理效率。
• 如果需要处理空字符串的输入，可以在WHERE条件中添加额外判断（比如@DepartmentName <> ''）。

内容的提问来源于stack exchange，提问作者akshata patil