USP_MKTLISTCONSOLIDATEDLIST_ADDTOSYSTEMROLEPERM_QUERYVIEW

CREATE procedure dbo.[USP_MKTLISTCONSOLIDATEDLIST_ADDTOSYSTEMROLEPERM_QUERYVIEW]
(
  @ID uniqueidentifier,  -- ID of consolidated query view spec

  @RECORDSOURCEID uniqueidentifier, --the record source

  @CHANGEAGENTID uniqueidentifier = null  
)
as
  set nocount on;

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

    insert into [SYSTEMROLEPERM_QUERYVIEW] (
      [SYSTEMROLEID],
      [QUERYVIEWCATALOGID],
      [GRANTORDENY],
      [ADDEDBYID],
      [CHANGEDBYID]
    )
    select distinct
      [SYSTEMROLE].[ID],
      @ID,
      1,
      @CHANGEAGENTID,
      @CHANGEAGENTID
    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 try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;