UFN_MKTSELECTION_SMARTQUERIESEXIST

Returns whether or not any smart queries exist with the available record source record types.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode	Description
@TYPE	tinyint	IN

Definition

Copy



CREATE function dbo.[UFN_MKTSELECTION_SMARTQUERIESEXIST]
(
  @TYPE tinyint  -- 1 = donor, 2 = revenue, 3 = membership, 4 = sponsorship

)
returns bit
as
begin
  declare @EXISTS bit;

  set @TYPE = isnull(@TYPE, 1);

  if @TYPE = 1 -- donor-based selections

    set @EXISTS = (case when exists(
                     select *
                     from dbo.[SMARTQUERYCATALOG]
                     where [RECORDTYPEID] in (select [ID] from dbo.[UFN_MKTRECORDSOURCE_GETRECORDTYPES](null, null, null)))
                   then 1 else 0 end);

  else if @TYPE = 2 -- revenue-based selections

    set @EXISTS = (case when exists(
                     select *
                     from dbo.[SMARTQUERYCATALOG]
                     where [RECORDTYPEID] in (select [ID] from dbo.[UFN_MKTGIFTRECORDSOURCE_GETRECORDTYPES](null, null)))
                   then 1 else 0 end);

  else if @TYPE = 3 -- membership-based selections

    set @EXISTS = (case when exists(
                     select *
                     from dbo.[SMARTQUERYCATALOG]
                     where [RECORDTYPEID] in (select [ID] from dbo.[UFN_MKTMEMBERSHIPRECORDSOURCE_GETRECORDTYPES](null, null)))
                   then 1 else 0 end);

  else if @TYPE = 4 -- sponsorship-based selections

    set @EXISTS = (case when exists(
                     select *
                     from dbo.[SMARTQUERYCATALOG]
                     where [RECORDTYPEID] in (select [ID] from dbo.[UFN_MKTSPONSORSHIPRECORDSOURCE_GETRECORDTYPES](null, null)))
                   then 1 else 0 end);

  else
    -- invalid selection type

    set @EXISTS = 0;

  return @EXISTS;
end