CODEWITHSUNDEEP
sqlsql-serverstored-procedures
acho818
acho818

Reputation: 25

Stored procedure for finding out if student is eligible for course with pre reqs

I have this stored procedure for a course registration system I am working on. My intention is to return a value of -1 if the query returns a course which a student has not taken according to the course pre requisites.

The pre req table only has two columns; CourseID being the course and PreReqCourse_ID being the required course for that specified course. If the student has taken all pre req courses then it should return a value of 1. I keep getting a value of -1 even when I run the query for a student who has taken the required pre req courses. Any help would be much appreciated!

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

Upvotes: 0

Views: 552

Answers (2)

Alex Gruebel
Alex Gruebel

Reputation: 86

You should JOIN the PreReq table with the Student_History on the the PreReq.PreReqCourse_ID and Student_History.CourseID columns (take a look at my example). Then your SP should work.

--create tmp example tables
IF OBJECT_ID('tempdb..#PreReq') IS NOT NULL DROP TABLE #PreReq

CREATE TABLE #PreReq(
     CourseID int,
     PreReqCourse_ID int
)

--insert Course 3 which depends on Course 2 and 1 in #PreReq
INSERT INTO #PreReq
values(3,2),(3,1)

IF OBJECT_ID('tempdb..#Student_History') IS NOT NULL DROP TABLE #Student_History

CREATE TABLE #Student_History(
     CourseID int not null,
     StudentID int not null
);

--insert Student 1 who has visited Course 1 and 2
insert into #Student_History
VALUES(1,1),(2,1)

--declare variables
DECLARE @CourseID AS INT = 3
       ,@StudentID AS INT = 1
       --COUNT on how many Courses @CourseID depends
       ,@necessaryCourses AS INT 
       --COUNT on how many Courses the Student has taken
       ,@countTakenCourses AS INT
       ,@theCount AS INT 

SET @necessaryCourses = (SELECT count(*) FROM #PreReq WHERE CourseID = @CourseID);


SET @countTakenCourses = (
    SELECT count(*)
    FROM #PreReq p
    --JOIN with Student_History to check if the student has visited the necessary course
    JOIN #Student_History h on p.PreReqCourse_ID = h.CourseID 
    WHERE p.CourseID = @CourseID AND h.StudentID = @StudentID
)

IF @necessaryCourses = @countTakenCourses  
BEGIN
    set @theCount = 1
END
ELSE 
BEGIN 
    set @theCount =  -1
END

SELECT @theCount AS theCount

Upvotes: 0

MJS
MJS

Reputation: 81

Would something like this work?

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

...or something like this:

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.

Upvotes: 1

Related Questions