USP_SMARTQUERY_GRANTAPPUSERROLES

CREATE procedure dbo.USP_SMARTQUERY_GRANTAPPUSERROLES
(
  @SMARTQUERYID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
as
begin
  --Use a merge statement in case the smart query system role permission already exists

  merge into dbo.SYSTEMROLEPERM_SMARTQUERY [TARGET]
  using (
    select SYSTEMROLE.ID
    from dbo.SYSTEMROLE
      inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLE.ID = SYSTEMROLEAPPUSER.SYSTEMROLEID
    where SYSTEMROLEAPPUSER.APPUSERID = @CURRENTAPPUSERID
  ) [SOURCE]
  on
    [SOURCE].ID = [TARGET].SYSTEMROLEID and [TARGET].SMARTQUERYCATALOGID = @SMARTQUERYID
  when not matched by target then
    insert (SYSTEMROLEID, GRANTORDENY, ID, SMARTQUERYCATALOGID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    values ([SOURCE].ID, 1, newid(), @SMARTQUERYID, @CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate());

end