USP_FAFGROUP_Change

CREATE procedure dbo.USP_FAFGROUP_Change
(
      @GROUPID                 uniqueidentifier,  
      @GROUPNAME               varchar(200),
      @TYPECODE                tinyint,
      @LINKGROUPCONSTITUENTID  uniqueidentifier
)
as
begin try
    -- do work

  Declare @ID  uniqueidentifier,
          @CHANGEAGENTID  uniqueidentifier,
          @CURRENTDATE    datetime ,
          @EVENTID        uniqueidentifier,
          @CURRENTTYPECODE  tinyint

  -- validation

  Select @EVENTID = EVENTID, @CURRENTTYPECODE = TYPECODE from dbo.teamExtension where TEAMFUNDRAISINGTEAMID =  @GROUPID

  if exists(Select tft.Name from TeamExtension te
                  inner join TEAMFUNDRAISINGTEAM tft on te.TEAMFUNDRAISINGTEAMID = tft.ID
              where te.EVENTID = @EVENTID and tft.ID != @GROUPID and tft.Name = @GROUPNAME)
   begin
       RAISERROR ('INUSEGROUPNAMEPEREVENT', 13, 1);
   end

  if exists(Select teamConstituentID from dbo.TeamExtension (nolock)
                where EVENTID = @EVENTID and teamConstituentID = @LINKGROUPCONSTITUENTID
                   and TEAMFUNDRAISINGTEAMID != @GROUPID)
   begin
        RAISERROR ('INUSEGROUPCONSTITUENTPEREVENT', 13, 1);
   end

  -- change name

  Update dbo.TeamFundraisingTeam Set Name = @GroupName Where ID = @GROUPID

  -- change link

  if @LINKGROUPCONSTITUENTID != CAST(CAST(0 AS BINARY) AS UNIQUEIDENTIFIER)
  begin
    Update dbo.TEAMExtension Set TeamConstituentID = @LINKGROUPCONSTITUENTID Where TEAMFUNDRAISINGTEAMID =  @GROUPID
  end

  -- do promotion/demotion

  if @CURRENTTYPECODE != @TYPECODE
  begin
     Update dbo.TEAMExtension Set TypeCode = @TYPECODE Where TEAMFUNDRAISINGTEAMID =  @GROUPID

     Update dbo.FAFFRIENDLYURLPARAMS Set PAGETYPE = @TYPECODE where PARTICIPANTID = @GROUPID

     Update dbo.Story Set TypeCode = @TYPECODE Where TEAMFUNDRAISINGTEAMID =  @GROUPID

     Exec USP_FAF_GROUP_PROMOTION_DEMOTION @GROUPID, @CURRENTTYPECODE, @TYPECODE, @EVENTID
  end

end try

begin catch  
  exec USP_RAISE_ERROR  
  return 1;  
end catch