USP_DATAFORMTEMPLATE_VIEW_ADHOCQUERYFOLDERDEFAULTQUERYPERMISSIONS

The load procedure used by the view dataform template "Ad-hoc Query Folder Default Query Permissions View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ALLOWALLUSERSTORUN	bit	INOUT	Allow all users to run
@ALLOWALLUSERSTOEDIT	bit	INOUT	Allow all users to edit
@SYSTEMROLELIST	xml	INOUT	System Role List
@SYSTEMROLELISTEDIT	xml	INOUT	System Role List
@SMARTQUERYALLOWALLUSERSTORUN	bit	INOUT	Allow all users to run
@SMARTQUERYALLOWALLUSERSTOEDIT	bit	INOUT	Allow other users to edit
@SMARTQUERYSYSTEMROLELIST	xml	INOUT	System Role List

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ADHOCQUERYFOLDERDEFAULTQUERYPERMISSIONS
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @ALLOWALLUSERSTORUN bit = null output,
                    @ALLOWALLUSERSTOEDIT bit = null output,
                    @SYSTEMROLELIST xml = null output,
                    @SYSTEMROLELISTEDIT xml = null output,
                    @SMARTQUERYALLOWALLUSERSTORUN bit = null output,
                    @SMARTQUERYALLOWALLUSERSTOEDIT bit = null output,
                    @SMARTQUERYSYSTEMROLELIST xml = null output
                )
                as
                    set nocount on

                    set @DATALOADED = 0

                    select
                        @DATALOADED = 1,
                        @ALLOWALLUSERSTORUN = QUERYDEFAULTALLOWALLUSERSTORUN,
                        @ALLOWALLUSERSTOEDIT = QUERYDEFAULTALLOWALLUSERSTOEDIT,
                        @SYSTEMROLELIST = 
                            case when QUERYDEFAULTALLOWALLUSERSTORUN = 0 then
                                (select 
                                    SYSTEMROLEID, 
                                    GRANTORDENY
                                from dbo.ADHOCQUERYFOLDERDEFAULTQUERYROLE
                                where
                                    ADHOCQUERYFOLDERID = @ID and
                                    PERMISSIONTYPECODE = 0 -- Run

                                for xml raw('ITEM'), type, elements, root('SYSTEMROLELIST'), BINARY BASE64)                                    
                            end,
                        @SYSTEMROLELISTEDIT =
                            case when QUERYDEFAULTALLOWALLUSERSTORUN = 0 then
                                (select 
                                    SYSTEMROLEID, 
                                    GRANTORDENY
                                from dbo.ADHOCQUERYFOLDERDEFAULTQUERYROLE
                                where
                                    ADHOCQUERYFOLDERID = @ID and
                                    PERMISSIONTYPECODE = 1 -- Edit

                                for xml raw('ITEM'), type, elements, root('SYSTEMROLELISTEDIT'), BINARY BASE64)                                    
                            end,
                        @SMARTQUERYALLOWALLUSERSTORUN = SMARTQUERYDEFAULTALLOWALLUSERSTORUN,
                        @SMARTQUERYALLOWALLUSERSTOEDIT = SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT,
                        @SMARTQUERYSYSTEMROLELIST =
                            (select 
                                SYSTEMROLEID, 
                                GRANTORDENY
                            from dbo.ADHOCQUERYFOLDERDEFAULTQUERYROLE
                            where
                                ADHOCQUERYFOLDERID = @ID and
                                PERMISSIONTYPECODE = 2 -- RunSmartQuery

                            for xml raw('ITEM'), type, elements, root('SMARTQUERYSYSTEMROLELIST'), BINARY BASE64)
                    from dbo.ADHOCQUERYFOLDER
                    where ID = @ID

                    return 0;