USP_MKTSEGMENTLIST_TOGGLEQUERYVIEWS

Toggles whether the list segment query views can be used or not by showing or hiding them wherever query views are used.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@ISROOTOBJECT	bit	IN
@CHANGEAGENTID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.[USP_MKTSEGMENTLIST_TOGGLEQUERYVIEWS]
(
  @ID uniqueidentifier,
  @ISROOTOBJECT bit,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @QUERYVIEWCATALOGID uniqueidentifier;
  declare @QUERYVIEWSPEC xml;
  declare @TOGGLE bit;

  begin try
    select
      @QUERYVIEWCATALOGID = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID],
      @QUERYVIEWSPEC = [QUERYVIEWCATALOG].[QUERYVIEWSPEC],
      @TOGGLE = (case when isnull([QUERYVIEWCATALOG].[ROOTOBJECT],0) = @ISROOTOBJECT then 0 else 1 end)
    from dbo.[MKTSEGMENT]
    inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    left join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]
    where [MKTSEGMENT].[ID] = @ID;

    if @QUERYVIEWCATALOGID is not null and @TOGGLE = 1
      begin
        if @CHANGEAGENTID is null
          exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

        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 sql:variable("@ISROOTOBJECT")');
        else
          set @QUERYVIEWSPEC.modify(N'declare namespace bbspec="bb_appfx_queryview"; insert attribute IsRoot {sql:variable("@ISROOTOBJECT")} into /bbspec:QueryViewSpec[1]');

        update dbo.[QUERYVIEWCATALOG] set
          [ROOTOBJECT] = @ISROOTOBJECT,
          [QUERYVIEWSPEC] = @QUERYVIEWSPEC,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = getdate()
        where [ID] = @QUERYVIEWCATALOGID;
      end
  end try

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

  return 0;