USP_MKTEXPORTDEFINITION_ADDTOSYSTEMROLEPERM_DATALIST

CREATE procedure dbo.[USP_MKTEXPORTDEFINITION_ADDTOSYSTEMROLEPERM_DATALIST]
(
  @EXPORTDEFINITIONID uniqueidentifier,
  @DATALISTCATALOGID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @MAILINGTYPECODE tinyint;
  declare @QUERYVIEWCATALOGID uniqueidentifier;
  declare @SYSTEMROLEID uniqueidentifier;

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

    set @MAILINGTYPECODE = dbo.[UFN_MKTEXPORTDEFINITION_GETMAILINGTYPECODEFROMID](@EXPORTDEFINITIONID);

    if @MAILINGTYPECODE = 0
      declare QUERYVIEWCATALOGCURSOR cursor local fast_forward for
      select [ID] from dbo.[MKTRECORDSOURCE];

    else if @MAILINGTYPECODE = 1 or @MAILINGTYPECODE = 5
      declare QUERYVIEWCATALOGCURSOR cursor local fast_forward for
      select [QUERYVIEWCATALOGID] from dbo.[MKTGIFTRECORDSOURCE];

    else if @MAILINGTYPECODE = 2
      declare QUERYVIEWCATALOGCURSOR cursor local fast_forward for
      select [QUERYVIEWCATALOGID] from dbo.[MKTMEMBERSHIPRECORDSOURCE];

    else if @MAILINGTYPECODE = 3
      declare QUERYVIEWCATALOGCURSOR cursor local fast_forward for
      select [QUERYVIEWCATALOGID] from dbo.[MKTSPONSORSHIPRECORDSOURCE];

    open QUERYVIEWCATALOGCURSOR;
    fetch next from QUERYVIEWCATALOGCURSOR into @QUERYVIEWCATALOGID;

    while (@@FETCH_STATUS = 0)
      begin
        declare SYSTEMROLECURSOR cursor local fast_forward for
          select distinct [SYSTEMROLE].[ID]
          from dbo.[SYSTEMROLE]
          inner join dbo.[SYSTEMROLEPERM_QUERYVIEW] on [SYSTEMROLE].[ID] = [SYSTEMROLEPERM_QUERYVIEW].[SYSTEMROLEID]
          inner join dbo.[QUERYVIEWCATALOG] on [SYSTEMROLEPERM_QUERYVIEW].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]
          inner join [RECORDTYPE] on [QUERYVIEWCATALOG].[RECORDTYPEID] = [RECORDTYPE].[ID]
          where [SYSTEMROLEPERM_QUERYVIEW].[QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID;

        open SYSTEMROLECURSOR;
        fetch next from SYSTEMROLECURSOR into @SYSTEMROLEID;

        while (@@FETCH_STATUS = 0)
        begin
          if not exists(select top 1 1 from dbo.[SYSTEMROLEPERM_DATALIST] where [SYSTEMROLEID] = @SYSTEMROLEID and [DATALISTCATALOGID] = @DATALISTCATALOGID)
            insert into dbo.[SYSTEMROLEPERM_DATALIST] (
              [SYSTEMROLEID],
              [DATALISTCATALOGID],
              [GRANTORDENY],
              [ADDEDBYID],
              [CHANGEDBYID]
            ) values (
              @SYSTEMROLEID,
              @DATALISTCATALOGID,
              1,
              @CHANGEAGENTID,
              @CHANGEAGENTID
            );

          fetch next from SYSTEMROLECURSOR into @SYSTEMROLEID;
        end

        close SYSTEMROLECURSOR;
        deallocate SYSTEMROLECURSOR;

        fetch next from QUERYVIEWCATALOGCURSOR into @QUERYVIEWCATALOGID;
      end

    close QUERYVIEWCATALOGCURSOR;
    deallocate QUERYVIEWCATALOGCURSOR;
  end try

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

  return 0;