USP_MKTRECORDSOURCE_ADDTOSYSTEMROLEPERM_QUERYVIEW

Grants permission to the marketing information and source code parts query views.

Parameters

Parameter	Parameter Type	Mode
@QUERYVIEWCATALOGID	uniqueidentifier	IN
@RECORDSOURCEID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.[USP_MKTRECORDSOURCE_ADDTOSYSTEMROLEPERM_QUERYVIEW]
(
  @QUERYVIEWCATALOGID uniqueidentifier,
  @RECORDSOURCEID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();

    insert into dbo.[SYSTEMROLEPERM_QUERYVIEW]
    (
      [SYSTEMROLEID],
      [QUERYVIEWCATALOGID],
      [GRANTORDENY],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select distinct
      [SYSTEMROLE].[ID],
      @QUERYVIEWCATALOGID,
      1,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    from dbo.[SYSTEMROLE]
    inner join dbo.[SYSTEMROLEPERM_QUERYVIEW] on [SYSTEMROLE].[ID] = [SYSTEMROLEPERM_QUERYVIEW].[SYSTEMROLEID]
    where [SYSTEMROLEPERM_QUERYVIEW].[QUERYVIEWCATALOGID] = @RECORDSOURCEID
    and not exists(select 1 from dbo.[SYSTEMROLEPERM_QUERYVIEW] where [SYSTEMROLEID] = [SYSTEMROLE].[ID] and [QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID);
  end try

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

  return 0;