USP_MKTMEMBERSHIPMAILINGTEMPLATE_DELETEPROMPT

create procedure dbo.[USP_MKTMEMBERSHIPMAILINGTEMPLATE_DELETEPROMPT]
(
  @ID uniqueidentifier,
  @PROMPTRESOURCEKEY nvarchar(100) output,
  @QUEUEPROCESSCOUNTEXCLUDINGFIRSTTEXT nvarchar(10) output,
  @FIRSTQUEUEPROCESSNAME nvarchar(255) output
)
as
  set nocount on;

  declare @QUEUEPROCESSCOUNT int = 0;
  declare @TEMPLATEID uniqueidentifier = @ID;

  /* membership mailing process */
  select @ID = [ID]
  from dbo.[MKTMEMBERSHIPMAILINGPROCESS]
  where [MEMBERSHIPMAILINGTEMPLATEID] = @TEMPLATEID;

  exec dbo.[USP_QUEUEPROCESS_GETBUSINESSPROCESSDELETEPROMPTINFO]
    @PARAMETERSETID = @ID,
    @BUSINESSPROCESSID = '854A7703-89D1-4EF7-A7C6-B8836A887092',
    @PROMPTRESOURCEKEY = @PROMPTRESOURCEKEY output,
    @QUEUEPROCESSCOUNT = @QUEUEPROCESSCOUNT output,
    @FIRSTQUEUEPROCESSNAME = @FIRSTQUEUEPROCESSNAME output,
    @QUEUEPROCESSCOUNTEXCLUDINGFIRSTTEXT = @QUEUEPROCESSCOUNTEXCLUDINGFIRSTTEXT output;

  /* also check for renewal effort processes */
  if @QUEUEPROCESSCOUNT = 0
    begin

      /* get the renewal process id */
      select @ID = [ID]
      from dbo.[MKTMEMBERSHIPRENEWALEFFORTPROCESS]
      where [MEMBERSHIPMAILINGTEMPLATEID] = @TEMPLATEID;

      exec dbo.[USP_QUEUEPROCESS_GETBUSINESSPROCESSDELETEPROMPTINFO]
        @PARAMETERSETID = @ID,
        @BUSINESSPROCESSID = 'ff236838-354e-455b-9ebc-0b3118c928c2',
        @PROMPTRESOURCEKEY = @PROMPTRESOURCEKEY output,
        @FIRSTQUEUEPROCESSNAME = @FIRSTQUEUEPROCESSNAME output,
        @QUEUEPROCESSCOUNTEXCLUDINGFIRSTTEXT = @QUEUEPROCESSCOUNTEXCLUDINGFIRSTTEXT output;

    end

  return 0;