USP_SIMPLEDATALIST_TRANSLATIONTABLEGRADE_SHARED

CREATE procedure dbo.USP_SIMPLEDATALIST_TRANSLATIONTABLEGRADE_SHARED
(   
    @TRANSLATIONTABLEID as uniqueidentifier = null,
    @VALUESALLOWED as int = 1
)
 as

   -- when used for grade/numeric dropdown, VALUESALLOWED is used to prevent items appearing in a dropdown for numeric


   -- return grades shared across all translation tables

    if (@TRANSLATIONTABLEID is null) or (@TRANSLATIONTABLEID = '00000000-0000-0000-0000-000000000000')
    begin
        declare @tt_count int = (select COUNT(ID) from dbo.TRANSLATIONTABLE);

        select 
            GRADE as VALUE,
            GRADE as LABEL
        from dbo.TRANSLATIONTABLEGRADE
        where @VALUESALLOWED <> 2
        group by GRADE    
        having count(GRADE) = @tt_count    -- only if exists in all TRANSLATIONTABLES

        order by max(NUMERICEQUIVALENT) desc
    end
    else    -- or specific table only

    begin
        select 
            GRADE as VALUE,
            GRADE as LABEL
        from dbo.TRANSLATIONTABLEGRADE
        where TRANSLATIONTABLEID = @TRANSLATIONTABLEID
            and @VALUESALLOWED <> 2
        order by NUMERICEQUIVALENT desc
    end