USP_DATALIST_GROUPMESSAGE

CREATE procedure dbo.USP_DATALIST_GROUPMESSAGE
(
  @TEAMFUNDRAISINGTEAMID uniqueidentifier 
)
as
    set nocount on;

    declare @parentGroupName nvarchar(200) = null
  declare @parentNFGName nvarchar(200) = null

  select @parentGroupName = isnull(T.NAME ,null)
  from dbo.TEAMFUNDRAISINGTEAM T (NOLOCK)
    where T.ID = (select PARENTTEAMID  from TEAMFUNDRAISINGTEAM where ID = @TEAMFUNDRAISINGTEAMID)

  select @parentNFGName = isnull(NFG.NAME, null)  
  from FAFNFGCAMPAIGN NFG
    left join FAFNFGCAMPAIGNLEVEL LVL ON NFG.ID = LVL.NFGCAMPAIGNID
    left join TEAMEXTENSION TE ON LVL.ID = TE.NFGCAMPAIGNLEVELID
    left join TEAMFUNDRAISINGTEAM T ON TE.TEAMFUNDRAISINGTEAMID = T.ID 
    where T.ID = @TEAMFUNDRAISINGTEAMID

  if (@parentGroupName is null) and (@parentNFGName is null)
      begin 
          select T.NAME as GROUPNAME
          from dbo.TEAMFUNDRAISINGTEAM T (NOLOCK)
          WHERE T.ID = @TEAMFUNDRAISINGTEAMID
      end
  else if @parentGroupName is not null
      begin
          select T.NAME + ' (' + @parentGroupName + ')' as GROUPNAME
          from dbo.TEAMFUNDRAISINGTEAM T (NOLOCK)
          WHERE T.ID = @TEAMFUNDRAISINGTEAMID
      end
  else if @parentNFGName is not null
      begin
          select T.NAME + ' (' + @parentNFGName + ')' as GROUPNAME
          from dbo.TEAMFUNDRAISINGTEAM T (NOLOCK)
          WHERE T.ID = @TEAMFUNDRAISINGTEAMID
      end