USP_APPUSER_GETAPPUSERINFO_FROM_BBIDNAMEIDENTIFIER

CREATE procedure dbo.USP_APPUSER_GETAPPUSERINFO_FROM_BBIDNAMEIDENTIFIER(
    @NAMEIDENTIFIER nvarchar(100),
    @INCLUDELINKS bit = 0
)
as
begin

    /*
        The APPUSERCLAIMSLINK allows application users in non-BBID environments to 
        be mapped to BBID users. Just in case there is an issue with migration, 
        always prioritize links in the APPUSERCLIAMS table.
    */
    select top 1
        ID,
        ISSYSADMIN,
        DISPLAYNAME,
        EMAILADDRESS
    from (    
        select
            APPUSER.ID,
            ISSYSADMIN,
            DISPLAYNAME,
            APPUSER.EMAILADDRESS,
            1 ORDERBY
        from 
            dbo.APPUSER
        inner join dbo.APPUSERCLAIMS on APPUSERCLAIMS.APPUSERID = APPUSER.ID
        where 
            APPUSERCLAIMS.NAMEIDENTIFIER = @NAMEIDENTIFIER

        union all            

        select
            APPUSER.ID,
            ISSYSADMIN,
            DISPLAYNAME,
            APPUSER.EMAILADDRESS,
            2 ORDERBY
        from 
            dbo.APPUSER
        inner join dbo.APPUSERCLAIMSLINK on APPUSERCLAIMSLINK.APPUSERID = APPUSER.ID
        where 
            APPUSERCLAIMSLINK.NAMEIDENTIFIER = @NAMEIDENTIFIER
            and @INCLUDELINKS = 1
    ) APPUSERINFO
    order by ORDERBY
end;