USP_DATALIST_SPONSORSHIPPROGRAMCMS

List of all the sponsorship programs associated with the opportunity query selected in the CMS

Parameters

Parameter	Parameter Type	Mode	Description
@QUERYID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



      CREATE procedure dbo.USP_DATALIST_SPONSORSHIPPROGRAMCMS
      (
        @QUERYID uniqueidentifier = null
      )
      as
        set nocount on;

          declare @IDSETREGISTERID uniqueidentifier = null -- selection id


          select @IDSETREGISTERID = IDSETREGISTERID 
          from dbo.IDSETREGISTERADHOCQUERY 
          where ADHOCQUERYID = @QUERYID

        if @IDSETREGISTERID is not null
            begin
                select distinct
                    SPONSORSHIPPROGRAM.ID,
                    SPONSORSHIPPROGRAM.NAME,    
                    case when SPONSORSHIPPROGRAM.ISINACTIVE = 1 then 'Inactive' else 'Active' end STATUS
                from dbo.SPONSORSHIPPROGRAM
                inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID = SPONSORSHIPPROGRAM.SPONSORSHIPOPPORTUNITYGROUPID
                inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) IDSET ON dbo.SPONSORSHIPOPPORTUNITY.ID = IDSET.ID
                where not exists (select 'x' from dbo.SPONSORSHIPAFFILIATEPROGRAM SAP where SAP.ID = SPONSORSHIPPROGRAM.ID)
                  order by SPONSORSHIPPROGRAM.NAME    
            end
        else
            begin
                select 
                    SPONSORSHIPPROGRAM.ID,
                    SPONSORSHIPPROGRAM.NAME,
                    case when SPONSORSHIPPROGRAM.ISINACTIVE = 1 then 'Inactive' else 'Active' end STATUS
                from dbo.SPONSORSHIPPROGRAM
                inner join dbo.SPONSORSHIPOPPORTUNITYGROUP on SPONSORSHIPOPPORTUNITYGROUP.ID = SPONSORSHIPPROGRAM.SPONSORSHIPOPPORTUNITYGROUPID
                where not exists (select 'x' from dbo.SPONSORSHIPAFFILIATEPROGRAM SAP where SAP.ID = SPONSORSHIPPROGRAM.ID)
                  order by SPONSORSHIPPROGRAM.NAME
            end