UFN_STUDENTMARKINGCOLUMNGRADE_VALIDGRADE

CREATE function dbo.UFN_STUDENTMARKINGCOLUMNGRADE_VALIDGRADE
(
    @STUDENTCLASSMEETINGGROUPID uniqueidentifier = null, 
    @COURSEGRADINGMARKINGCOLUMNID uniqueidentifier = null,
    @TRANSLATIONTABLEGRADEID uniqueidentifier = null,
    @NUMERICGRADE nvarchar(14) = null,
    @GRADETYPECODE tinyint
)
returns bit
with execute as caller
as begin
    -- do work here and return a value

    declare @retval bit = 0;
    declare @CASTNUMERICGRADE numeric(14,2) = null;
    if (@NUMERICGRADE <> '')
        set @CASTNUMERICGRADE = CONVERT(numeric(14, 2), @NUMERICGRADE)

    if exists 
        (select * from dbo.STUDENTCLASSMEETINGGROUP
        inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
        inner join dbo.CLASS on CLASSMEETINGGROUP.CLASSID = CLASS.ID
        inner join dbo.V_COURSEGRADING as COURSEGRADING on CLASS.COURSEID = COURSEGRADING.COURSEID
        inner join dbo.COURSEGRADINGMARKINGCOLUMN on COURSEGRADING.ID = COURSEGRADINGMARKINGCOLUMN.COURSEGRADINGID
        where (CLASSMEETINGGROUP.STARTDATE >= COURSEGRADING.STARTDATE and CLASSMEETINGGROUP.ENDDATE <= COURSEGRADING.ENDDATE) and 
            STUDENTCLASSMEETINGGROUP.ID = @STUDENTCLASSMEETINGGROUPID and
            COURSEGRADINGMARKINGCOLUMN.ID = @COURSEGRADINGMARKINGCOLUMNID)
        begin

        --Blank grades are always valid

        if (@CASTNUMERICGRADE is null and @TRANSLATIONTABLEGRADEID is null)
            set @retval = 1

        else if (@GRADETYPECODE = 1)
            begin
                --Letter grade

                if (@CASTNUMERICGRADE is null) and 
                    exists (select * from dbo.TRANSLATIONTABLEGRADE
                        inner join dbo.COURSEGRADINGMARKINGCOLUMN on TRANSLATIONTABLEGRADE.TRANSLATIONTABLEID = COURSEGRADINGMARKINGCOLUMN.TRANSLATIONTABLEID
                        where COURSEGRADINGMARKINGCOLUMN.ID = @COURSEGRADINGMARKINGCOLUMNID and
                            COURSEGRADINGMARKINGCOLUMN.GRADED = 1 and
                            TRANSLATIONTABLEGRADE.ID = @TRANSLATIONTABLEGRADEID and
                            ((COURSEGRADINGMARKINGCOLUMN.VALUESALLOWEDCODE = @GRADETYPECODE) or (COURSEGRADINGMARKINGCOLUMN.VALUESALLOWEDCODE = 0)))
                    set @retval = 1;
            end

        else if (@GRADETYPECODE = 2)
            begin
                --Numeric grade

                if (@TRANSLATIONTABLEGRADEID is null) and 
                    exists (select * from dbo.TRANSLATIONTABLE
                        inner join dbo.COURSEGRADINGMARKINGCOLUMN on TRANSLATIONTABLE.ID = COURSEGRADINGMARKINGCOLUMN.TRANSLATIONTABLEID
                        where COURSEGRADINGMARKINGCOLUMN.ID = @COURSEGRADINGMARKINGCOLUMNID and
                            COURSEGRADINGMARKINGCOLUMN.GRADED = 1 and
                            (@CASTNUMERICGRADE between TRANSLATIONTABLE.LOWESTSCOREALLOWED and TRANSLATIONTABLE.HIGHESTSCOREALLOWED) and
                            ((COURSEGRADINGMARKINGCOLUMN.VALUESALLOWEDCODE = @GRADETYPECODE) or (COURSEGRADINGMARKINGCOLUMN.VALUESALLOWEDCODE = 0)))
                    set @retval = 1;
            end
        end
    return @retval
end