UFN_TRANSLATIONTABLE_GAPEXISTS

create function dbo.UFN_TRANSLATIONTABLE_GAPEXISTS
(
    @TRANSLATIONTABLEID uniqueidentifier = null
)
returns smallint
with execute as caller
as begin
    declare @HIGH smallint, @LOW smallint
    set @HIGH = 0
    set @LOW = 0

    if exists(select * from TRANSLATIONTABLEGRADE ttg where TRANSLATIONTABLEID = @TRANSLATIONTABLEID)
    begin
        if not exists (select * from TRANSLATIONTABLEGRADE ttg 
                       join dbo.TRANSLATIONTABLE tt on ttg.TRANSLATIONTABLEID = tt.ID
                       where tt.ID = @TRANSLATIONTABLEID 
                       and ttg.LOWESTSCORE = tt.LOWESTSCOREALLOWED)
            set @LOW = 1;

        if not exists (select * from TRANSLATIONTABLEGRADE ttg 
                       join dbo.TRANSLATIONTABLE tt on ttg.TRANSLATIONTABLEID = tt.ID
                       where tt.ID = @TRANSLATIONTABLEID 
                       and ttg.HIGHESTSCORE = tt.HIGHESTSCOREALLOWED)
            set @HIGH = 2;
    end

    return @HIGH + @LOW;
end