SQL Server: Check constraint

Question

I am trying to create a check constraint in SQL Server.

I have a table called Studies which has 'pnr', 'courseCode', 'assignment'.
I have a table called Student which has 'pnr'.
I have a table called Course which has 'courseCode'.
I have a table called Assignment which has 'courseCode', 'assignment', 'assignmentPoints'.

Now I want to check that prevents the admin from inserting a pnr into Studies if that pnr is currently already attending courses worth to much points, the limit is 45 points.

I have gotten this far but it doesn't work.

create function EnrollmentFunction (@pnr varchar(10)) returns varchar(10)
as
begin
    if exists (
             select sum(assignmentPoints) as Points 
             from   Assignment a 
               join Studies s
                 on a.courseCode = s.courseCode 
                    and a.assignmentName = s.assignmentName 
                    and a.sectionName = s.sectionName 
                    and pnr = @pnr
             where assignmentPoints > 45)
        return 'False'
    return 'True'
end

---

alter table Studies
with check add constraint CK_Points
check (dbo.EnrollmentFunction(pnr) = 'True')

---

However when I run insert on that specific student and add a course when that student already is above the limit for points it goes through, the check is not preventing the insert.

Please help!

Answer 1

create function EnrollmentFunction 
(@pnr varchar(10)) 
returns varchar(10)
as
begin
  DECLARE @RtnValue varchar(10);

    if exists ( select sum(assignmentPoints) as Points from Assignment a join Studies s 
on a.courseCode = s.courseCode and a.assignmentName = s.assignmentName and a.sectionName = s.sectionName and pnr = @pnr
where assignmentPoints > 45
               )
       BEGIN
          SET @RtnValue = 'True'
       END
    ELSE 
       BEGIN
         SET @RtnValue = 'False'
       END

   RETURN @RtnValue;    
end

This does not work.. It will return 'False' if the student is taking courses worth 45 points and it will return 'False' if he does not. So either the result is something less than 45, say for example 35. Or it will say null. Either way the result is 'False'.

Answer 2

Dont know the logic of your function but syntax should look something like this.....

create function EnrollmentFunction 
(@pnr varchar(10)) 
returns varchar(10)
as
begin
  DECLARE @RtnValue varchar(10);

    if exists ( select 1 
                from Assignment a 
                join Studies s   on a.courseCode = s.courseCode 
                                and a.assignmentName = s.assignmentName 
                                and a.sectionName = s.sectionName 
                                and pnr = @pnr
                where assignmentPoints > 45
               )
       BEGIN
          SET @RtnValue = 'False'
       END
    ELSE 
       BEGIN
         SET @RtnValue = 'True'
       END

   RETURN @RtnValue;    
end