USP_MKTSEGMENTLIST_ADDTOSYSTEMROLEPERM_QUERYVIEW

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

  declare @RECORDSOURCEID uniqueidentifier;
  declare @CURRENTDATE datetime;

  begin try
    if not exists(select 1 from dbo.[SYSTEMROLEPERM_QUERYVIEW] inner join dbo.[SYSTEMROLE] on [SYSTEMROLEPERM_QUERYVIEW].[SYSTEMROLEID] = [SYSTEMROLE].[ID] where [SYSTEMROLEID] = [SYSTEMROLE].[ID] and [QUERYVIEWCATALOGID] = @ID)
      begin
        if @CHANGEAGENTID is null
          exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

        select
          @RECORDSOURCEID = [MKTLIST].[RECORDSOURCEID]
        from dbo.[MKTSEGMENTLIST]
        inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
        where [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = @ID;

        set @CURRENTDATE = getdate();

        insert into dbo.[SYSTEMROLEPERM_QUERYVIEW] (
          [SYSTEMROLEID],
          [QUERYVIEWCATALOGID],
          [GRANTORDENY],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        )
        select distinct
          [SYSTEMROLE].[ID],
          @ID,
          1,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        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 dbo.[RECORDTYPE] on [QUERYVIEWCATALOG].[RECORDTYPEID] = [RECORDTYPE].[ID]
        where [SYSTEMROLEPERM_QUERYVIEW].[QUERYVIEWCATALOGID] = @RECORDSOURCEID
        and [SYSTEMROLEPERM_QUERYVIEW].[QUERYVIEWCATALOGID] <> @ID;
      end
  end try

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

  return 0;