USP_DATAFORMTEMPLATE_VIEW_SMARTQUERY_DEFINITIONS

The load procedure used by the view dataform template "Smart Query Source View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SMARTQUERYDEFINITIONS_COMMON	xml	INOUT	SMARTQUERYDEFINITIONS_COMMON
@SMARTQUERYDEFINITIONS	xml	INOUT	Select a Query Type

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SMARTQUERY_DEFINITIONS
(
    @CURRENTAPPUSERID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @SMARTQUERYDEFINITIONS_COMMON xml = null output,
    @SMARTQUERYDEFINITIONS xml = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 1;

    declare @DEFINITIONS table
    (
        ID uniqueidentifier,
        NAME nvarchar(60),     
        RECORDTYPE nvarchar(50),
        [DESCRIPTION] nvarchar(1000)
    )

    insert into @DEFINITIONS
    (
        ID,
        NAME,
        [DESCRIPTION],
        RECORDTYPE
    )
    select
        SMARTQUERYCATALOG.ID, 
        SMARTQUERYCATALOG.NAME, 
        SMARTQUERYCATALOG.DESCRIPTION, 
        RECORDTYPE.NAME 'RECORDTYPE'
    from dbo.SMARTQUERYCATALOG
        inner join dbo.RECORDTYPE on SMARTQUERYCATALOG.RECORDTYPEID = RECORDTYPE.ID
    where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
            or dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_IN_SYSTEMROLE(@CURRENTAPPUSERID,SMARTQUERYCATALOG.ID) = 1
        )
        and 1 = dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED
                    (
                        SMARTQUERYCATALOG.[SMARTQUERYSPEC].query
                            (
                                'declare namespace common="bb_appfx_commontypes";
                                /*/common:InstalledProductList'
                            )
                    )
    order by RECORDTYPE.NAME, SMARTQUERYCATALOG.NAME

    select
          @SMARTQUERYDEFINITIONS = (
                select
                      DEFINITIONS.ID,
                      DEFINITIONS.NAME,
                      DEFINITIONS.RECORDTYPE as GROUPTYPE,
                      DEFINITIONS.DESCRIPTION     
                from @DEFINITIONS DEFINITIONS
                order by GROUPTYPE, NAME
                for xml raw('ITEM'),type,elements,root('SMARTQUERYDEFINITIONS'),binary base64
          ),
          @SMARTQUERYDEFINITIONS_COMMON = (
                select top(5)
                      DEFINITIONS.ID,
                      DEFINITIONS.NAME,
                      DEFINITIONS.RECORDTYPE as GROUPTYPE,
                      DEFINITIONS.DESCRIPTION
                from @DEFINITIONS DEFINITIONS
                      inner join dbo.SMARTQUERYINSTANCE on DEFINITIONS.ID = SMARTQUERYINSTANCE.SMARTQUERYCATALOGID
                group by
                      DEFINITIONS.ID,
                      DEFINITIONS.NAME,
                      DEFINITIONS.RECORDTYPE,
                      DEFINITIONS.DESCRIPTION
                order by COUNT(SMARTQUERYINSTANCE.ID) desc
                for xml raw('ITEM'),type,elements,root('SMARTQUERYDEFINITIONS_COMMON'),binary base64
          )

    return 0;