USP_MKTSOURCEANALYSISRULE_ADDTOSYSTEMROLEPERM_QUERYVIEW

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

  declare @RECORDSOURCEID uniqueidentifier;

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

    select
      @RECORDSOURCEID = [RECORDSOURCEID]
    from
      dbo.[MKTSOURCEANALYSISRULES]
    where
      [QUERYVIEWSPECID] = @ID;
    if not exists(select 1 from [SYSTEMROLEPERM_QUERYVIEW] inner join [SYSTEMROLE] on [SYSTEMROLEPERM_QUERYVIEW].[SYSTEMROLEID] = [SYSTEMROLE].[ID] where [SYSTEMROLEID] = [SYSTEMROLE].[ID] and [QUERYVIEWCATALOGID] = @ID)
    begin
      insert into [SYSTEMROLEPERM_QUERYVIEW] (
        [SYSTEMROLEID],
        [QUERYVIEWCATALOGID],
        [GRANTORDENY],
        [ADDEDBYID],
        [CHANGEDBYID]
      )
      select distinct
        [SYSTEMROLE].[ID],
        @ID,
        1,
        @CHANGEAGENTID,
        @CHANGEAGENTID
      from
        dbo.[SYSTEMROLE]
      inner join
        [SYSTEMROLEPERM_QUERYVIEW] on [SYSTEMROLE].[ID] = [SYSTEMROLEPERM_QUERYVIEW].[SYSTEMROLEID]
      inner join
        [QUERYVIEWCATALOG] on [SYSTEMROLEPERM_QUERYVIEW].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]
      inner join
        [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;