USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIPOPPORTUNITYGROUP

The load procedure used by the view dataform template "Sponsorship Opportunity Group Information 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.
@NAME	nvarchar(100)	INOUT	Name
@SPONSORSHIPOPPORTUNITYTYPE	int	INOUT	Group type
@OPPORTUNITIES	int	INOUT	Opportunities
@SPONSORSPEROPPORTUNITY	nvarchar(9)	INOUT	Sponsors per opportunity
@OFFERSOLESPONSORSHIP	bit	INOUT	Offer sole sponsorship
@LOCATION	nvarchar(max)	INOUT	Location
@GENDER	nvarchar(100)	INOUT	Gender
@AGERANGE	nvarchar(100)	INOUT	Age Range
@HIVPOSITIVE	nvarchar(100)	INOUT	HIV Positive
@ORPHANED	nvarchar(100)	INOUT	Orphaned
@HASCONDITION	nvarchar(100)	INOUT	Disability/Illness
@STATUS	nvarchar(100)	INOUT	Status
@SPROPPPROJECTCATEGORYCODE	nvarchar(100)	INOUT	Category

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIPOPPORTUNITYGROUP
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NAME nvarchar(100) = null output,
    @SPONSORSHIPOPPORTUNITYTYPE int = null output,
    @OPPORTUNITIES int = null output,
    @SPONSORSPEROPPORTUNITY nvarchar(9) = null output,
    @OFFERSOLESPONSORSHIP bit = null output,
    @LOCATION nvarchar(max) = null output,
    @GENDER nvarchar(100) = null output,
    @AGERANGE nvarchar(100) =null output,
    @HIVPOSITIVE nvarchar(100) =null output,
    @ORPHANED nvarchar(100) =null output,
    @HASCONDITION nvarchar(100)=null output,
    @STATUS nvarchar(100)=null output,
    @SPROPPPROJECTCATEGORYCODE nvarchar(100) = 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,
        @NAME = SG.NAME,
        @SPONSORSHIPOPPORTUNITYTYPE = SG.SPONSORSHIPOPPORTUNITYTYPECODE,
        @OPPORTUNITIES = dbo.UFN_SPONSORSHIPOPPORTUNITIES_FOR_GROUP(SG.ID),
        @SPONSORSPEROPPORTUNITY = case when SG.SPONSORSPEROPPORTUNITY = 0 then 'Unlimited' else cast(SG.SPONSORSPEROPPORTUNITY as nvarchar) end,
        @OFFERSOLESPONSORSHIP = SG.OFFERSOLESPONSORSHIP,
        @LOCATION = case when SG.SPONSORSHIPLOCATIONID is not null then dbo.UFN_SPONSORSHIPLOCATION_GETFULLNAME(SG.SPONSORSHIPLOCATIONID) else 'Any' end,
        @GENDER = SG.GENDER,
        @AGERANGE = case when SOAR.DISPLAYNAME <> '' then SOAR.DISPLAYNAME else 'Any' end,
        @HIVPOSITIVE = SG.ISHIVPOSITIVE,
        @ORPHANED = SG.ISORPHANED,
        @HASCONDITION = SG.HASCONDITION,
        @SPROPPPROJECTCATEGORYCODE = isnull(SPROPPPROJECTCATEGORYCODE.DESCRIPTION,'Any'),
        @STATUS = case when SG.ISINACTIVE = 1 then 'Inactive' else 'Active' end
    from dbo.SPONSORSHIPOPPORTUNITYGROUP SG
    left join SPONSORSHIPOPPORTUNITYAGERANGE SOAR on SOAR.ID = SG.SPONSORSHIPOPPORTUNITYAGERANGEID
    left join SPROPPPROJECTCATEGORYCODE on SPROPPPROJECTCATEGORYCODE.ID = SG.SPROPPPROJECTCATEGORYCODEID
    where @ID = SG.ID;

    return 0;