USP_DATAFORMTEMPLATE_VIEW_ADHOCQUERYFOLDERROLES

The load procedure used by the view dataform template "Ad-Hoc Query Folder Roles View Data 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.
@SYSTEMROLELIST	xml	INOUT	System role list
@QUERYDEFAULTRUNROLES	xml	INOUT	Query default run roles
@QUERYDEFAULTEDITROLES	xml	INOUT	Query default edit roles
@SMARTQUERYDEFAULTRUNROLES	xml	INOUT	Smart Query default run roles
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ADHOCQUERYFOLDERROLES
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @SYSTEMROLELIST xml = null output,
                    @QUERYDEFAULTRUNROLES xml = null output,
                    @QUERYDEFAULTEDITROLES xml = null output,
                    @SMARTQUERYDEFAULTRUNROLES xml = null output
                )
                as
                    set nocount on;

                    select
                        @DATALOADED = 1,
                        @SYSTEMROLELIST = 
                            (select 
                                SR.ID as SYSTEMROLEID, 
                                SR.NAME, 
                                coalesce((select GRANTORDENY
                                          from dbo.SYSTEMROLEPERM_ADHOCQUERYFOLDER as SRP 
                                          where
                                            SRP.SYSTEMROLEID = SR.ID and
                                            SRP.ADHOCQUERYFOLDERID = @ID), 2) as GRANTORDENY
                            from
                                dbo.SYSTEMROLE as SR 
                            order by SR.NAME
                            for xml raw('ITEM'), type, elements, root('SYSTEMROLELIST'), BINARY BASE64),
                        @QUERYDEFAULTRUNROLES = 
                            (select
                                ID as SYSTEMROLEID,
                                NAME, 
                                coalesce((select GRANTORDENY
                                          from dbo.ADHOCQUERYFOLDERDEFAULTQUERYROLE as SRP 
                                          where
                                            SRP.SYSTEMROLEID = SYSTEMROLE.ID and 
                                            SRP.ADHOCQUERYFOLDERID = @ID and
                                            PERMISSIONTYPECODE = 0 -- Run

                                            ), 2) as GRANTORDENY
                            from
                                dbo.SYSTEMROLE
                            order by NAME
                            for xml raw('ITEM'), type, elements, root('QUERYDEFAULTRUNROLES'), BINARY BASE64),
                        @QUERYDEFAULTEDITROLES = 
                            (select
                                ID as SYSTEMROLEID,
                                NAME, 
                                coalesce((select GRANTORDENY
                                          from dbo.ADHOCQUERYFOLDERDEFAULTQUERYROLE as SRP 
                                          where 
                                            SRP.SYSTEMROLEID = SYSTEMROLE.ID and 
                                            SRP.ADHOCQUERYFOLDERID = @ID and
                                            PERMISSIONTYPECODE = 1 -- Edit

                                            ), 2) as GRANTORDENY
                            from
                                dbo.SYSTEMROLE
                            order by NAME
                            for xml raw('ITEM'), type, elements, root('QUERYDEFAULTEDITROLES'), BINARY BASE64),
                        @SMARTQUERYDEFAULTRUNROLES = 
                            (select
                                ID as SYSTEMROLEID,
                                NAME, 
                                coalesce((select GRANTORDENY
                                          from dbo.ADHOCQUERYFOLDERDEFAULTQUERYROLE as SRP 
                                          where
                                            SRP.SYSTEMROLEID = SYSTEMROLE.ID and 
                                            SRP.ADHOCQUERYFOLDERID = @ID and
                                            PERMISSIONTYPECODE = 2 -- RunSmartQuery

                                            ), 2) as GRANTORDENY
                            from
                                dbo.SYSTEMROLE
                            order by NAME
                            for xml raw('ITEM'), type, elements, root('SMARTQUERYDEFAULTRUNROLES'), BINARY BASE64)