UFN_ADHOCQUERY_ISBROWSABLE

CREATE function dbo.UFN_ADHOCQUERY_ISBROWSABLE(@QUERYDEFINITIONXML xml, 
    @ROOTOBJECT nvarchar(255),
    @PRIMARYKEYFIELD nvarchar(255))
returns bit
as
begin
    declare @RETURN_VALUE bit;

    declare @QUERYVIEWHASPRIMARYKEYFIELD bit;
    declare @SELECTSAGGREGATE bit;
    declare @SELECTSPRIMARYKEYFIELD bit;

    set @SELECTSAGGREGATE = 0;
    set @SELECTSPRIMARYKEYFIELD = 0;

    --If the query view doesn't have a primary key field specified, the ad-hoc query can't be browsed. Otherwise, check to see if the

    --ad-hoc query contains an aggregate but not the primary key.

    if not @PRIMARYKEYFIELD is null and @PRIMARYKEYFIELD <> ''
    begin

        set @QUERYVIEWHASPRIMARYKEYFIELD = 1;

        --Check for the presence of an aggregate in the ad-hoc query's output.

        with xmlnamespaces ('Blackbaud.AppFx.WebService.API.1' as [ns])
        select @SELECTSAGGREGATE = 1
        from @QUERYDEFINITIONXML.nodes('ns:AdHocQuery/ns:SelectFields/ns:f[string-length(@AggregateType) > 0]') as T(c);

        --Check for the presence of the primary key with no aggregate type in the ad-hoc query's output.

        with xmlnamespaces ('Blackbaud.AppFx.WebService.API.1' as [ns])
        select @SELECTSPRIMARYKEYFIELD = 1
        from @QUERYDEFINITIONXML.nodes('ns:AdHocQuery/ns:SelectFields/ns:f[string-length(@AggregateType) = 0]') as T(c)
        where T.c.value('@ObjectName', 'nvarchar(255)') = @ROOTOBJECT
        and T.c.value('@ColumnName', 'nvarchar(255)') = @PRIMARYKEYFIELD;

    end;

    if @QUERYVIEWHASPRIMARYKEYFIELD = 1 and (@SELECTSAGGREGATE = 0 or @SELECTSPRIMARYKEYFIELD = 1)
        set @RETURN_VALUE = 1;
    else
        set @RETURN_VALUE = 0;

    return @RETURN_VALUE;
end;