UFN_COURSEGRADINGMARKINGCOLUMN_ISVALIDTRANSLATIONTABLE

CREATE function dbo.UFN_COURSEGRADINGMARKINGCOLUMN_ISVALIDTRANSLATIONTABLE
(
    @COURSEGRADINGMARKINGCOLUMNID uniqueidentifier,
    @TRANSLATIONTABLEID uniqueidentifier,
    @VALUESALLOWED tinyint
)
returns bit
as begin
    declare @retval bit = 1;

    if (@VALUESALLOWED = 1 or @VALUESALLOWED = 0) and (@TRANSLATIONTABLEID is not null)
    begin
        if (select sum(case when SELECTED.ID is null then 1 else 0 end) from dbo.STUDENTMARKINGCOLUMNGRADE
            inner join dbo.TRANSLATIONTABLEGRADE EXISTING on STUDENTMARKINGCOLUMNGRADE.TRANSLATIONTABLEGRADEID = EXISTING.ID
            left outer join dbo.TRANSLATIONTABLEGRADE SELECTED on EXISTING.GRADE = SELECTED.GRADE and 
                SELECTED.TRANSLATIONTABLEID = @TRANSLATIONTABLEID
            where STUDENTMARKINGCOLUMNGRADE.GRADEISBLANK = 0 and
                STUDENTMARKINGCOLUMNGRADE.GRADETYPECODE = 1 and
                STUDENTMARKINGCOLUMNGRADE.COURSEGRADINGMARKINGCOLUMNID = @COURSEGRADINGMARKINGCOLUMNID) > 0
        set @retval = 0;
    end    


    if (@VALUESALLOWED = 2 or @VALUESALLOWED = 0) and (@TRANSLATIONTABLEID is not null)
    begin
        if (select sum(case when SELECTED.ID is null then 1 else 0 end) from dbo.STUDENTMARKINGCOLUMNGRADE
            left outer join dbo.TRANSLATIONTABLE SELECTED on SELECTED.ID = @TRANSLATIONTABLEID and
                STUDENTMARKINGCOLUMNGRADE.NUMERICGRADE between SELECTED.LOWESTSCOREALLOWED and SELECTED.HIGHESTSCOREALLOWED                 
            where STUDENTMARKINGCOLUMNGRADE.GRADEISBLANK = 0 and
                STUDENTMARKINGCOLUMNGRADE.GRADETYPECODE = 2 and
                STUDENTMARKINGCOLUMNGRADE.COURSEGRADINGMARKINGCOLUMNID = @COURSEGRADINGMARKINGCOLUMNID) > 0
        set @retval = 0;
    end    

    return @retval
end