USP_FAFEVENTCAMPAIGNSITE_COPY

create procedure dbo.USP_FAFEVENTCAMPAIGNSITE_COPY
(    
  @SOURCEID uniqueidentifier,
  @TARGETEVENTCAMPAIGNID uniqueidentifier,  
  @CHANGEAGENTID uniqueidentifier = null
) 
as
begin
    set nocount on;  

  if @CHANGEAGENTID is null  
    exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output  

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()

  CREATE TABLE #TEMPTBL
  (
    ID int identity(1,1),
    SITEID uniqueidentifier
  ) 
  insert into #TEMPTBL select   
     SITEID from dbo.FAFPROGRAMSITE where FAFPROGRAMID = @SOURCEID 

  declare @COUNT int, @INDEX int, @SITEID uniqueidentifier
  select @COUNT = COUNT(*),@INDEX = 1  from #TEMPTBL
  while @INDEX< = @Count
    begin
     select @SITEID = SITEID from #TEMPTBL where ID = @INDEX
     if not exists(select 1 from dbo.FAFPROGRAMSITE where FAFPROGRAMID = @TARGETEVENTCAMPAIGNID and SITEID = @SITEID)
        insert into dbo.FAFPROGRAMSITE
        (ID, FAFPROGRAMID, SITEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) 
        values
        (newid(), @TARGETEVENTCAMPAIGNID, @SITEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)   
     set @INDEX = @INDEX + 1
    end
  return 0;  
end