USP_MKTLIST_MARKACTIVE

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

  declare @SEGMENTID uniqueidentifier;

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

    --Mark the list as "Active"...

    update dbo.[MKTLIST] set
      [ISINACTIVE] = 0,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = getdate()
    where [ID] = @ID;

    --Unhide the query views for all list segments based on this list that have selections defined.  We don't

    --know for sure which query views were visible before the user marked the list inactive, so we are just

    --taking a best guess here and assuming that only segments with selections should have visible query views.

    declare SEGMENTLISTCURSOR cursor local fast_forward for
      select
        [MKTSEGMENT].[ID]
      from dbo.[MKTSEGMENT]
      inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
      inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]
      where [MKTSEGMENTLIST].[LISTID] = @ID
      and [MKTSEGMENTLIST].[TYPECODE] = 0    --Imported

      and [MKTSEGMENTLIST].[STATUSCODE] = 3  --Active

      and exists(select top 1 1 from dbo.[ADHOCQUERY] where [QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]);

    open SEGMENTLISTCURSOR;
    fetch next from SEGMENTLISTCURSOR into @SEGMENTID;

    while (@@FETCH_STATUS = 0)
    begin
      exec dbo.[USP_MKTSEGMENTLIST_TOGGLEQUERYVIEWS] @SEGMENTID, 1, @CHANGEAGENTID;
      fetch next from SEGMENTLISTCURSOR into @SEGMENTID;
    end

    close SEGMENTLISTCURSOR;
    deallocate SEGMENTLISTCURSOR;
  end try

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

  return 0;