USP_DATAFORMTEMPLATE_EDIT_SMARTQUERY_PERMISSIONS

The save procedure used by the edit dataform template "Smart Query Permissions Edit Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@SYSTEMROLELIST	xml	IN	System Role List

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SMARTQUERY_PERMISSIONS
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,    
    @SYSTEMROLELIST xml
)
as
set nocount on;

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

declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();

begin try
    declare @contextCache varbinary(128);

    --cache current context information
    set @contextCache = CONTEXT_INFO();

    --set CONTEXT_INFO to @CHANGEAGENTID
    set CONTEXT_INFO @CHANGEAGENTID;

    update dbo.SYSTEMROLE 
        set CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
    where ID = @ID;

    -- build a temporary table containing the values from the XML, excluding ones where permission is None
    declare @TEMPTABLE table
    (
        ID uniqueidentifier,
        SYSTEMROLEID uniqueidentifier,
        GRANTORDENY bit
    );
    insert into @TEMPTABLE
        select ID, SYSTEMROLEID, GRANTORDENY
        from dbo.UFN_SMARTQUERY_GETSYSTEMROLEPERMISSIONSLIST_FROMITEMLISTXML(@SYSTEMROLELIST)
        where (GRANTORDENY = 0) or (GRANTORDENY = 1) 

    -- rebuild the system role list
    set @SYSTEMROLELIST =
        (
            select ID, SYSTEMROLEID, GRANTORDENY
            from @TEMPTABLE 
            for xml raw('ITEM'), type, elements, root('SYSTEMROLELIST'), binary base64
        );

    if @SYSTEMROLELIST is null
        delete from dbo.SYSTEMROLEPERM_SMARTQUERY where SMARTQUERYCATALOGID = @ID;
    else
        exec dbo.USP_SMARTQUERY_GETSYSTEMROLEPERMISSIONSLIST_UPDATEFROMXML @SYSTEMROLELIST;

    --reset CONTEXT_INFO to previous value
    if not @contextCache is null
        set CONTEXT_INFO @contextCache;

    return 0;
end try
begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
end catch