时间戳

首页 > 解决方案 > 确定学生是否有资格参加具有先决条件的课程的存储过程

sql - 确定学生是否有资格参加具有先决条件的课程的存储过程

问题描述

我有一个我正在研究的课程注册系统的存储过程。如果查询返回学生未根据课程先决条件学习的课程，我的意图是返回值 -1。

pre req 表只有两列；CourseID是该课程并且PreReqCourse_ID是该指定课程的必修课程。如果学生已修完所有 pre req 课程，那么它应该返回值 1。即使我为已修读所需 pre req 课程的学生运行查询，我仍然会得到 -1 的值。任何帮助将非常感激！

CREATE PROCEDURE CheckPreReq 
    @StudentID INT, 
    @CourseID INT
AS
    DECLARE @theCount INT

    IF EXISTS (SELECT * 
               FROM PreReq 
               INNER JOIN Student_History ON (PreReq.Course_ID = @CourseID)  
               WHERE Student_History.Course_ID != PreReq.PreReqCourse_ID
                 AND Student_History.Student_ID = @StudentID)
    BEGIN
        SET @theCount =-1
    END
    ELSE
    BEGIN
        SET @theCount = 1
    END

    RETURN @theCount

标签： sqlsql-serverstored-procedures

解决方案

像这样的东西会起作用吗？

DECLARE @PreReqsTotal       tinyint
DECLARE @PreReqsFulfilled   tinyint

-- Count pre-req courses.
SELECT  @PreReqsTotal   = COUNT(*)
FROM    PreReq 
WHERE   [CourseID]      = @CourseId

-- Count how many fulfilled by student.
SELECT  @PreReqsFulfilled = count(*)
FROM    Student_History         hist
JOIN    PreReq                  pre
    on  hist.Course_ID  = pre.PreReqCourse_ID
WHERE   pre.CourseID    = @CourseID
    and hist.Student_ID = @StudentID

RETURN CASE WHEN @PreReqsTotal = @PreReqsFulfilled THEN 1 ELSE -1 END

...或类似的东西：

IF EXISTS
(
    SELECT  blah.*
    FROM
    (
        SELECT   pre.*
                ,[PreFulfilled]     = CASE WHEN hist.Course_ID is null THEN 0 ELSE 1 END
        FROM    PreReq              pre
        LEFT JOIN
                Student_History     hist
            on  pre.PreReqCourse_ID = hist.Course_ID
            and hist.Student_ID = @StudentID
        WHERE   pre.CourseID    = @CourseID
    ) blah
    WHERE   blah.[PreFulfilled] = 0         -- Unfulfilled PreReq.
)
BEGIN
    RETURN -1       -- Has an unfulfilled PreReq.
END
    RETURN 1        -- No unfulfilled PreReqs.

推荐阅读