UFN_DESIGNATIONLEVEL_GETIDFROMREVENUEID

        create function dbo.UFN_DESIGNATIONLEVEL_GETIDFROMREVENUEID(
          @REVENUEID uniqueidentifier
        )
        returns uniqueidentifier
        as begin

          declare @LEVEL1 uniqueidentifier,@LEVEL2 uniqueidentifier,@LEVEL3 uniqueidentifier,@LEVEL4 uniqueidentifier,@LEVEL5 uniqueidentifier

           select
            @LEVEL1 = [DESIGNATION].[DESIGNATIONLEVEL1ID],
            @LEVEL2 = [DESIGNATION].[DESIGNATIONLEVEL2ID],
            @LEVEL3 = [DESIGNATION].[DESIGNATIONLEVEL3ID],
            @LEVEL4 = [DESIGNATION].[DESIGNATIONLEVEL4ID],
            @LEVEL5 = [DESIGNATION].[DESIGNATIONLEVEL5ID]
            from dbo.[Revenue] Rev inner join dbo.[REVENUESPLIT] RS 
           on RS.[REVENUEID] = Rev.[ID]
           inner join dbo.[DESIGNATION]
           on RS.[DESIGNATIONID] = DESIGNATION.[ID]
            where Rev.ID = @REVENUEID

            declare @DESIGNATIONLEVEL uniqueidentifier

            if (@LEVEL5 is not null)
             set @DESIGNATIONLEVEL = @LEVEL5
            else
             if (@LEVEL4 is not null)
              set @DESIGNATIONLEVEL = @LEVEL4
              else
                if(@LEVEL3 is not null)
                 set @DESIGNATIONLEVEL = @LEVEL3
                 else
                  if (@LEVEL2 is not null)
                   set @DESIGNATIONLEVEL = @LEVEL2
                   else
                    if (@LEVEL1 is not null)
                     set @DESIGNATIONLEVEL = @LEVEL1
                     else
                     return null

            return @DESIGNATIONLEVEL

           end