USP_DATAFORMTEMPLATE_EDIT_SMARTQUERYINSTANCE_PERMISSIONS

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

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.
@SECURITYLEVEL	tinyint	IN	This feature is available for
@SYSTEMROLELIST	xml	IN	System roles

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SMARTQUERYINSTANCE_PERMISSIONS(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,    
    @SECURITYLEVEL tinyint,
    @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;

    if @SECURITYLEVEL = 1
        begin    
            -- build a temporary table containing the values from the XML, excluding ones where permission is None

            declare @TempTbl table (ID uniqueidentifier, SYSTEMROLEID uniqueidentifier, GRANTORDENY bit)
            insert into @TempTbl 
                select ID, SYSTEMROLEID, GRANTORDENY
                from dbo.UFN_SMARTQUERYINSTANCE_GETSYSTEMROLEPERMISSIONSLIST_FROMITEMLISTXML(@SYSTEMROLELIST)
                where (GRANTORDENY = 0) or (GRANTORDENY = 1)

            -- rebuild the system role list

            set @SYSTEMROLELIST = 
                (select ID, SYSTEMROLEID, GRANTORDENY
                 from @TempTbl
                 for xml raw('ITEM'), type, elements, root('SYSTEMROLELIST'), BINARY BASE64)

            if @SYSTEMROLELIST is null
                delete from dbo.SYSTEMROLEPERM_SMARTQUERYINSTANCE where SMARTQUERYID = @ID
            else
                exec dbo.USP_SMARTQUERYINSTANCE_GETSYSTEMROLEPERMISSIONSLIST_UPDATEFROMXML @ID, @SYSTEMROLELIST, @CHANGEAGENTID;
        end
    else
        -- remove rows from dbo.SYSTEMROLEPERM_SMARTQUERYINSTANCE

        delete from dbo.SYSTEMROLEPERM_SMARTQUERYINSTANCE where SMARTQUERYID = @ID

    -- now update the flag on the smart query table

    update dbo.SMARTQUERYINSTANCE set SECURITYLEVEL = @SECURITYLEVEL, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
        where (ID = @ID) and (SECURITYLEVEL <> @SECURITYLEVEL)

    --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