UFN_ADHOCQUERY_IDSMARTFIELDISINUSEBY

Examines the XML definition of each ad-hoc query in the database to determine whether the given query view field is in use by an existing ad-hoc query.

Return

Return Type
nvarchar(255)

Parameters

Parameter	Parameter Type	Mode	Description
@SMARTFIELDNAME	nvarchar(125)	IN

Definition

Copy



create function dbo.UFN_ADHOCQUERY_IDSMARTFIELDISINUSEBY(@SMARTFIELDNAME nvarchar(125))
returns nvarchar(255)
as
begin

    declare @RETURNVALUE nvarchar(255);

        with xmlnamespaces ('Blackbaud.AppFx.WebService.API.1' as [ns])
        select @RETURNVALUE = ADHOCQUERY.NAME
        from dbo.ADHOCQUERY
        where exists ((select T.c.value('@ColumnName', 'nvarchar(255)') 
            from QUERYDEFINITIONXML.nodes('ns:AdHocQuery/*/ns:f[@ColumnName]') as T(c)
            where
               (T.c.value('@ParentPath', 'nvarchar(max)') like '%' + isnull(@SMARTFIELDNAME, '') + '%')));

    if @RETURNVALUE is null set @RETURNVALUE = '';

    return @RETURNVALUE;
end;