USP_SPONSOR_GETINFORMATION

Get sponsor status and type. Used to determine proper sponsor name for view as section on a page.

Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@ISSPONSOR	bit	INOUT
@ISFORMERSPONSOR	bit	INOUT
@SPONSORTYPECODE	tinyint	INOUT

Definition

Copy



create procedure dbo.USP_SPONSOR_GETINFORMATION(
  @CONSTITUENTID as uniqueidentifier = null,
  @ISSPONSOR as bit = null output,
  @ISFORMERSPONSOR as bit = null output,
  @SPONSORTYPECODE as tinyint = null output)
as
begin
      declare @CURRENTDATE date = getdate()

      SELECT 
        @ISSPONSOR =  CASE MAX(ALLRECS.ISACTIVE)
                      WHEN 1 THEN 1
                      ELSE 0
                      END,
        @ISFORMERSPONSOR = CASE MAX(ALLRECS.ISACTIVE)
                      WHEN 0 THEN 1
                      ELSE 0
                      END ,
            @SPONSORTYPECODE = CASE MAX(ALLRECS.ISACTIVE)
                          WHEN 1  THEN CASE 
                                  WHEN MIN(ALLRECS.ACTIVE_STC) = MAX(ALLRECS.ACTIVE_STC) THEN MIN(ALLRECS.ACTIVE_STC)
                                  ELSE 0
                                  END
                          WHEN 0 THEN CASE
                                 WHEN MIN(ALLRECS.INACTIVE_STC) = MAX(ALLRECS.INACTIVE_STC) THEN MIN(ALLRECS.INACTIVE_STC)
                                 ELSE 0
                                  END    
                          END     
        FROM
       (SELECT 
          CASE when(SDR.DATEFROM <= @CURRENTDATE or SDR.DATEFROM is null) and
                      (SDR.DATETO > @CURRENTDATE or SDR.DATETO is null) then 1
              else 0
          end ISACTIVE,
          CASE when(SDR.DATEFROM <= @CURRENTDATE or SDR.DATEFROM is null) and
                      (SDR.DATETO > @CURRENTDATE or SDR.DATETO is null) then SDR.SPONSORTYPECODE
          end ACTIVE_STC,
          CASE when (SDR.DATETO <= @CURRENTDATE) then SDR.SPONSORTYPECODE
          end INACTIVE_STC,       
          SPONSORTYPECODE STC
       FROM
       dbo.SPONSORDATERANGE SDR where SDR.CONSTITUENTID = @CONSTITUENTID) ALLRECS
end