Problem when trying to convert CHECK with subqueries to a function in SQL

Question

I've been sitting with this problem for hours and I'm on the verge of going crazy. I want each Student to only be able to study a maximum of 45 courseCredits. At this point it prohibits me from adding more than a total of 45 CourseCredits. How am I supposed to rearrange the Function CheckCredits?

CREATE TABLE Student
(
    studentID VARCHAR(5),
    studentName VARCHAR(20),
    studentAddress VARCHAR(20),
    CONSTRAINT student_pk PRIMARY KEY(studentID)
)

CREATE TABLE Course
(
    courseID VARCHAR(5),
    courseCredits INT,
    courseName VARCHAR(20),
    CONSTRAINT course_pk PRIMARY KEY(courseID)
)

CREATE TABLE Studies
(
    studentID VARCHAR(5),
    courseID VARCHAR(5),
    CONSTRAINT studies_pk PRIMARY KEY(studentID,courseID),
    CONSTRAINT studies_fk_student FOREIGN KEY(studentID) REFERENCES Student(studentID) ON DELETE CASCADE,
    CONSTRAINT studies_fk_course FOREIGN KEY(courseID) REFERENCES Course(courseID) ON DELETE CASCADE
)

CREATE TABLE HasStudied
(
    studentID VARCHAR(5),
    courseID VARCHAR(5),
    grade VARCHAR(1),
    CONSTRAINT has_studied_pk PRIMARY KEY(studentID,courseID),
    CONSTRAINT has_studied_fk_student FOREIGN KEY(studentID) REFERENCES Student(studentID) ON DELETE CASCADE,
    CONSTRAINT has_studied_fk_course FOREIGN KEY(courseID) REFERENCES Course(courseID) ON DELETE CASCADE
)

GO
CREATE FUNCTION CheckCredits()
RETURNS INT
AS 
BEGIN
   DECLARE @returnvalue INT
   SELECT @returnvalue = (SELECT SUM(courseCredits) FROM Course c where c.courseID IN(
    SELECT s.courseID FROM Studies s JOIN Student st ON s.studentID = st.studentID OR c.courseID = s.courseID))
   RETURN @returnvalue
END;
GO

ALTER TABLE Studies 
ADD CONSTRAINT chkCredits CHECK (dbo.CheckCredits() <= 45);  



INSERT INTO Student VALUES('S1', 'Joe', 'Street')
INSERT INTO Student VALUES('S2', 'Joe', 'Street')
INSERT INTO Student VALUES('S3', 'Joe', 'Street')
INSERT INTO Student VALUES('S4', 'Joe', 'Street')

INSERT INTO Course VALUES('C1', 45, 'Biology')
INSERT INTO Course VALUES('C2', 15, 'History')
INSERT INTO Course VALUES('C3', 35, 'English')
INSERT INTO Course VALUES('C4', 20, 'Music')

INSERT INTO Studies VALUES('S1', 'C1')
INSERT INTO Studies VALUES('S2', 'C2')
INSERT INTO Studies VALUES('S3', 'C3')

Answer 1

Change your function to return the max credits per student. Like this:

CREATE FUNCTION CheckCredits()
RETURNS INT
AS 
BEGIN
    DECLARE @returnvalue INT
    SELECT TOP 1 @returnvalue = SUM(courseCredits) 
    FROM Studies s 
    JOIN Course c ON s.courseID = c.courseID 
    GROUP BY s.studentID 
    ORDER BY SUM(courseCredits)  desc 

   RETURN @returnvalue
END;
GO