USP_MKTSEGMENTLIST_HIDEPREVIOUSQUERYVIEWS

create procedure dbo.[USP_MKTSEGMENTLIST_HIDEPREVIOUSQUERYVIEWS]
(
  @SEGMENTID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @QUERYVIEWCATALOGID uniqueidentifier;
  declare @QUERYVIEWSPEC xml;
  declare @CURRENTDATE datetime;

  begin try
    if @CHANGEAGENTID is null
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    set @CURRENTDATE = getdate();

    declare QVCURSOR cursor local fast_forward for
      select
        [MKTSEGMENTLIST].[QUERYVIEWCATALOGID],
        [QUERYVIEWCATALOG].[QUERYVIEWSPEC]
      from dbo.[MKTSEGMENT]
      inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[SEGMENTID] = [MKTSEGMENT].[ID]
      left join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]
      where [MKTSEGMENT].[ID] = @SEGMENTID
      and [MKTSEGMENT].[CURRENTSEGMENTLISTID] <> [MKTSEGMENTLIST].[ID]
      and [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] is not null
      and [QUERYVIEWCATALOG].[ROOTOBJECT] = 1;

    open QVCURSOR;
    fetch next from QVCURSOR into @QUERYVIEWCATALOGID, @QUERYVIEWSPEC;

    while (@@FETCH_STATUS = 0)
    begin
      if @QUERYVIEWSPEC.exist(N'declare namespace bbspec="bb_appfx_queryview"; /bbspec:QueryViewSpec[1]/@IsRoot') = 1
        set @QUERYVIEWSPEC.modify(N'declare namespace bbspec="bb_appfx_queryview"; replace value of /bbspec:QueryViewSpec[1]/@IsRoot with "false"');
      else
        set @QUERYVIEWSPEC.modify(N'declare namespace bbspec="bb_appfx_queryview"; insert attribute IsRoot {"false"} into /bbspec:QueryViewSpec[1]');

      update dbo.[QUERYVIEWCATALOG] set
        [ROOTOBJECT] = 0,
        [QUERYVIEWSPEC] = @QUERYVIEWSPEC,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @QUERYVIEWCATALOGID;

      fetch next from QVCURSOR into @QUERYVIEWCATALOGID, @QUERYVIEWSPEC;
    end

    close QVCURSOR;
    deallocate QVCURSOR;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;