EA7_spUpdate_CalendarEvtCategoryPicks

CREATE procedure [dbo].[EA7_spUpdate_CalendarEvtCategoryPicks](
  @SiteContentID int,
  @CategoryList nvarchar(4000)
  )

  AS

  set nocount on

  --Step 1 - We are going to delete the old category picks for ALL instances of the team calendars
  --         that are derived from this part instance.

  --  Step 1a - First, save the IDs of the category picks we are deleting because we have to delete
  --            them from the actual security tables later
  declare @DeletedSecurityObjects table(id uniqueidentifier)
  insert into @DeletedSecurityObjects(id)(
      select CalendarCategoryPicksID from dbo.CalendarEvtCategoryPicks
      where eventcalendarid in (select id from dbo.eventcalendar where sitecontentid=@sitecontentid)
  )

  --  Step 1b - Perform the delete
  delete from dbo.CalendarEvtCategoryPicks
  where eventcalendarid in (select id from dbo.eventcalendar where sitecontentid=@sitecontentid)

  --Step 2 - Save the new category picks. This will save the picks for all personalized
  --         calendars that are derived from the calendar defined by param @sitecontentid
  insert into dbo.CalendarEvtCategoryPicks(EventCalendarID, CategoryCodeTableEntryID)
  (
  select ec.id, f.g
  from dbo.EventCalendar ec
  left join fnMakeGuidsTableFromString(@CategoryList, ',') f  
  on ec.ID=ec.ID
  where ec.SiteContentID = @SiteContentID
  )

  --Step 3 - Set up security so "everyone" can view events created in these categories

  --  Step 3a - Delete the (now irrelevant) security privs based on info we saved in step 1a
  delete from dbo.CMSEVERYONESECURITYPRIVS where SECUREDOBJECTGUID in
  (select ID from @DeletedSecurityObjects)

  --  Step 3b - Define vars for audit trail, and insert into CMSEVERYONESECURITYPRIVS table
  declare @OBJECTTASKID uniqueidentifier
  set @OBJECTTASKID = (select ID from dbo.CMSOBJECTTASK where ENUMID = 0)

  declare @CURRENTDATE datetime = getdate();
  declare @CHANGEAGENTID uniqueidentifier;
  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  insert into dbo.CMSEVERYONESECURITYPRIVS(
    SECUREDOBJECTGUID, SECUREDOBJECTTYPEID, OBJECTTASKID, VALUE
    ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
  )
  (
    select CalendarCategoryPicksID, 31, @OBJECTTASKID, 1
    ,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from CalendarEvtCategoryPicks
    where CategoryCodeTableEntryID in (select f.g from fnMakeGuidsTableFromString(@CategoryList, ',') f)
    and eventcalendarid in (select id from dbo.EventCalendar where SiteContentID=@SiteContentID)
  )