USP_MKTSMARTFIELD_ADDTOSYSTEMROLEPERM_QUERYVIEW

CREATE procedure dbo.[USP_MKTSMARTFIELD_ADDTOSYSTEMROLEPERM_QUERYVIEW]
(
  @OBJECTNAME nvarchar(128),
  @RECORDSOURCEID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @QUERYVIEWCATALOGID uniqueidentifier;
  declare @GRANTORDENY bit;
  declare @SYSTEMROLEID uniqueidentifier;

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

    select
      @QUERYVIEWCATALOGID = [ID]
    from
      dbo.[QUERYVIEWCATALOG]
    where
      [OBJECTNAME] = @OBJECTNAME;

    declare roles_cursor cursor local fast_forward for
    select distinct [SYSTEMROLEID], [GRANTORDENY] from dbo.[SYSTEMROLEPERM_QUERYVIEW]
    where [QUERYVIEWCATALOGID] =  @RECORDSOURCEID;

    open roles_cursor;
    fetch next from roles_cursor into @SYSTEMROLEID, @GRANTORDENY;

    while @@FETCH_STATUS = 0
    begin

      if not exists(select 1 from [SYSTEMROLEPERM_QUERYVIEW] inner join [SYSTEMROLE] on [SYSTEMROLEPERM_QUERYVIEW].[SYSTEMROLEID] = [SYSTEMROLE].[ID] where [SYSTEMROLEID] = @SYSTEMROLEID and [QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID)
      begin
        insert into [SYSTEMROLEPERM_QUERYVIEW] (
          [SYSTEMROLEID],
          [QUERYVIEWCATALOGID],
          [GRANTORDENY],
          [ADDEDBYID],
          [CHANGEDBYID]
        ) values (
          @SYSTEMROLEID,
          @QUERYVIEWCATALOGID,
          @GRANTORDENY,
          @CHANGEAGENTID,
          @CHANGEAGENTID
        )
      end
      else
      begin
        update [SYSTEMROLEPERM_QUERYVIEW]
        set [GRANTORDENY] = @GRANTORDENY
        where [SYSTEMROLEID] = @SYSTEMROLEID and [QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID;
      end

      fetch next from roles_cursor into @SYSTEMROLEID, @GRANTORDENY;
    end
    close roles_cursor;
    deallocate roles_cursor;
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;