UFN_TRANSLATIONFUNCTION_MANAGEPURPOSEDESIGNATIONINFORMATIONTRANSLATION

CREATE function dbo.UFN_TRANSLATIONFUNCTION_MANAGEPURPOSEDESIGNATIONINFORMATIONTRANSLATION
(
    @ID uniqueidentifier
)
returns nvarchar(100)
as
begin
    declare @s nvarchar(100) = null;

    --PurposeReportCode1 or DesignationReportCode1

    select
        @s = DESCRIPTION
    from 
        dbo.DESIGNATIONREPORT1CODE
    where ID = @ID;

    if @s is not null
        return @s;

    --PurposeReportCode2 or DesignationReportCode2

    select
        @s = DESCRIPTION
    from 
        dbo.DESIGNATIONREPORT2CODE
    where ID = @ID;

    if @s is not null
        return @s;

    --IsRevenueDesignation

    select
        @s = 'Yes'
    where @ID = '00000000-0000-0000-0000-000000000001'

    select
        @s = 'No'
    where @ID = '00000000-0000-0000-0000-000000000002'

    if @s is not null
        return @s;

    --UseCode

    select
        @s = DESCRIPTION
    from 
        dbo.DESIGNATIONUSECODE
    where ID = @ID;

    if @s is not null
        return @s;

    --VSECategory

    select
        @s = VSECATEGORY
    from
        dbo.VSECATEGORY
    where ID = @ID;

    if @s is not null
        return @s;

    --VESSubCategory

    select
        @s = NAME
    from
        dbo.VSESUBCATEGORY
    where ID = @ID;

    if @s is not null
        return @s;

    -- Purpose Records

    select
        @s = DESCRIPTION
    from
        dbo.DESIGNATIONLEVELCATEGORYCODE
    where ID = @ID;

    if @s is not null
        return @s;

    --Site

    select
        @s = NAME
    from
        dbo.SITE
    where ID = @ID;

    if @s is not null
        return @s;

    return N'';

end