USP_DATAFORMTEMPLATE_VIEW_EVENTSPONSORPAGEDATA

The load procedure used by the view dataform template "Event Sponsor Page Expression View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@EVENTSPONSORID	uniqueidentifier	INOUT	EVENTSPONSORID
@EVENTID	uniqueidentifier	INOUT	EVENTID
@EVENTNAME	nvarchar(100)	INOUT	EVENTNAME
@SPONSORNAME	nvarchar(173)	INOUT	SPONSORNAME
@ISORGANIZATION	bit	INOUT	ISORGANIZATION
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@CONSTITUENTNAME	nvarchar(173)	INOUT	Name
@ORGANIZATIONCONSTITUENTID	uniqueidentifier	INOUT	ORGANIZATIONCONSTITUENTID
@ORGANIZATIONNAME	nvarchar(173)	INOUT	Company name
@ISCANCELLED	bit	INOUT	ISCANCELLED
@EMAILADDRESSID	uniqueidentifier	INOUT	EMAILADDRESSID
@ORGANIZATIONEMAILADDRESSID	uniqueidentifier	INOUT	ORGANIZATIONEMAILADDRESSID
@SPONSORCONSTITUENTID	uniqueidentifier	INOUT	SPONSORCONSTITUENTID

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EVENTSPONSORPAGEDATA
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @EVENTSPONSORID uniqueidentifier = null output,
  @EVENTID uniqueidentifier = null output,
  @EVENTNAME nvarchar(100) = null output,
  @SPONSORNAME nvarchar(173) = null output,
  @ISORGANIZATION bit = null output,
  @CONSTITUENTID uniqueidentifier = null output,
  @CONSTITUENTNAME nvarchar(173) = null output,
  @ORGANIZATIONCONSTITUENTID uniqueidentifier = null output,
  @ORGANIZATIONNAME nvarchar(173) = null output,
  @ISCANCELLED bit = null output,
  @EMAILADDRESSID uniqueidentifier = null output,
  @ORGANIZATIONEMAILADDRESSID uniqueidentifier = null output,
  @SPONSORCONSTITUENTID uniqueidentifier = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

    -- populate the output parameters, which correspond to fields on the form.  Note that
    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
    -- will display a "no data loaded" message.
    select @DATALOADED = 1,
    @EVENTSPONSORID = ES.ID,
        @EVENTID = ES.EVENTID,
        @EVENTNAME = E.NAME,
        @SPONSORNAME = isnull(OC.NAME, IC.NAME),
        @ISORGANIZATION = case when ES.ORGANIZATIONCONSTITUENTID is null then 0 else 1 end,
        @CONSTITUENTID = ES.CONSTITUENTID,
        @CONSTITUENTNAME = IC.NAME,
        @ORGANIZATIONCONSTITUENTID = ES.ORGANIZATIONCONSTITUENTID,
        @ORGANIZATIONNAME = OC.NAME,
    @ISCANCELLED = ES.ISCANCELLED,
    @EMAILADDRESSID = IE.ID,
    @ORGANIZATIONEMAILADDRESSID = OE.ID,
    @SPONSORCONSTITUENTID = isnull(OC.ID, IC.ID)
    from dbo.EVENTSPONSOR ES(nolock)
    left outer join dbo.[EVENT] E(nolock)on ES.EVENTID = E.ID
    left outer join dbo.CONSTITUENT IC(nolock)
        on IC.ID = ES.CONSTITUENTID
    left outer join dbo.EMAILADDRESS IE(nolock)
        on IE.CONSTITUENTID = IC.ID and IE.ISPRIMARY = 1
  left outer join dbo.CONSTITUENT OC(nolock)
        on OC.ID = ES.ORGANIZATIONCONSTITUENTID
  left outer join dbo.EMAILADDRESS OE(nolock)
        on OE.CONSTITUENTID = OC.ID and OE.ISPRIMARY = 1
    where ES.ID = @ID

    return 0;