UFN_ADHOCQUERY_FIELDISINUSEBY

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
@OBJECTNAME	nvarchar(255)	IN
@FIELDNAME	nvarchar(255)	IN

Definition

Copy



            create function dbo.UFN_ADHOCQUERY_FIELDISINUSEBY(@OBJECTNAME nvarchar(255), @FIELDNAME nvarchar(255))
            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('@ObjectName', 'nvarchar(255)') = @OBJECTNAME
                            and ((@FIELDNAME = '') or (T.c.value('@ColumnName', 'nvarchar(255)') = @FIELDNAME))));

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

                return @RETURNVALUE;
            end;