USP_USERPAGEHISTORY_DELETEBYCONTEXTRECORD

create procedure dbo.USP_USERPAGEHISTORY_DELETEBYCONTEXTRECORD
(
    @CONTEXTRECORDID nvarchar(100),
    @RECORDTYPE nvarchar(100),
    @APPUSERID uniqueidentifier
)
as
begin

    declare @PAGES dbo.UDT_GENERICID  -- This is a temp table with just an ID column


    -- Find the pages where the contextrecordid is part of this user's history

    insert into @PAGES
    select distinct PAGEID
    from dbo.USERPAGEHISTORY
    inner join dbo.PAGEDEFINITIONCATALOG on USERPAGEHISTORY.PAGEID = PAGEDEFINITIONCATALOG.ID
    inner join dbo.RECORDTYPE on RECORDTYPE.ID = PAGEDEFINITIONCATALOG.CONTEXTRECORDTYPEID
    where APPUSERID = @APPUSERID and RECORDTYPE.NAME = @RECORDTYPE and
        HISTORY.exist(N'/UserPageHistory/HistoryItemList/h[fn:upper-case(@ContextRecordID)=fn:upper-case(sql:variable("@CONTEXTRECORDID"))]') = 1

    -- Alter the history on each of the pages, removing all instances of that contextrecordid

    update dbo.USERPAGEHISTORY
    set HISTORY.modify(N'delete /UserPageHistory/HistoryItemList/h[fn:upper-case(@ContextRecordID)=fn:upper-case(sql:variable("@CONTEXTRECORDID"))]')
    where APPUSERID = @APPUSERID and PAGEID in (select ID from @PAGES)

    -- Delete any histories that were just reduced to 0 entries

    if @@RowCount > 0
    delete from dbo.USERPAGEHISTORY
    where APPUSERID = @APPUSERID and PAGEID in (select ID from @PAGES)
        and HISTORY.exist(N'/UserPageHistory/HistoryItemList/h') <> 1

    select ID from @PAGES

end