USP_DATALIST_SELECTIONSCMSSEARCH

Used by the CMS to search for selections.

Parameters

Parameter	Parameter Type	Mode	Description
@NAME	nvarchar(100)	IN	Name
@RECORDTYPEID	uniqueidentifier	IN	Record Type ID
@INCLUDESTATIC	bit	IN	Include Static
@INCLUDEDYNAMIC	bit	IN	Include Dynamic
@EXCLUDEDIDS	xml	IN	Excluded IDs
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
@MAXROWS	int	IN	Input parameter indicating the maximum number of rows to return.

Definition

Copy



create procedure dbo.USP_DATALIST_SELECTIONSCMSSEARCH
(
@NAME nvarchar(100) = null,
@RECORDTYPEID uniqueidentifier = null,
@INCLUDESTATIC bit = 1,
@INCLUDEDYNAMIC bit = 1,
@EXCLUDEDIDS xml = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@MAXROWS int = 1000000
)
as
    set nocount on;
    ---We want to base our security on USP_DATALIST_SELECTIONS

    set @SECURITYFEATUREID = 'B6C067F2-DE8C-48DE-950F-CDF631B81E73'
    set @SECURITYFEATURETYPE = 2

    ---First check that we have rights to the above DataList at all

    if exists(select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE))
    begin

        declare @EXCLUDED table (ID int)
        insert into @EXCLUDED
        select
        ids.id.value('.', 'int')
        from @EXCLUDEDIDS.nodes('ids/id') ids(id)

        declare @ISSYSADMIN bit;
        select @ISSYSADMIN = [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID;

        select
            top(@MAXROWS)
            IDSR.[ID] AS SELECTIONID,
            BBNCIDSETIDMAP.ID as INTEGERID,
            IDSR.[NAME] as [NAME],
            IDSR.STATIC as Static,
            RECORDTYPE.NAME as RECORDTYPE,
            IDSR.DATEADDED,
            coalesce(IDSR.[NUMROWS],0) as NUMROWS
        from 
            dbo.IDSETREGISTER as IDSR        
            inner join dbo.BBNCIDSETIDMAP on IDSR.ID = BBNCIDSETIDMAP.IDSETREGISTERID
            left outer join    dbo.RECORDTYPE on IDSR.RECORDTYPEID = RECORDTYPE.ID
            -- Filter by installed products

            left join dbo.SQLFUNCTIONCATALOG on SQLFUNCTIONCATALOG.FUNCTIONNAME = IDSR.DBOBJECTNAME        
            left outer join @EXCLUDED E on E.ID = BBNCIDSETIDMAP.ID
        where 
            E.ID is null
            and (IDSR.STATIC = 0 OR @INCLUDESTATIC = 1)
            and (IDSR.STATIC = 1 OR @INCLUDEDYNAMIC = 1)
            and
            (SQLFUNCTIONCATALOG.ID is null or 
                dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED (
                SQLFUNCTIONCATALOG.SQLFUNCTIONSPECXML.query (
                      'declare namespace common="bb_appfx_commontypes";
                      /*/common:InstalledProductList'
                )
            ) = 1)
            and ((@RECORDTYPEID is null) or (IDSR.RECORDTYPEID = @RECORDTYPEID))
            and ((@NAME is null or @NAME = '') or (IDSR.NAME like @NAME + '%'))
            and not exists(select [ID] from dbo.[IMPORTSELECTIONPROCESS] where [IDSETREGISTERID] = [IDSR].[ID])

            -- note that @CURRENTAPPUSERID is optional to maintain backwards compatibility

            and ((@ISSYSADMIN = 1) or (@CURRENTAPPUSERID is null) or (dbo.UFN_SECURITY_APPUSER_GRANTED_SELECTION_IN_SYSTEMROLE(@CURRENTAPPUSERID, IDSR.ID) = 1))
            and
            (
                IDSR.SITEID is null 
                or
                (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[IDSR].[SITEID] or (SITEID is null and [IDSR].[SITEID] is null)))
            )
            --and (ACTIVE = 1) For now we wont restrict this since the BBNC Service selection search doesn't either

            and IDSR.ISSYSTEM = 0;
        end