USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIPOPPORTUNITYCHILDDETAILCRITERIA

The load procedure used by the view dataform template "Sponsorship Opportunity Child Detail 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.
@ISHIVPOSITIVE	nvarchar(20)	INOUT	HIV positive
@ISORPHANED	nvarchar(20)	INOUT	Orphaned
@CONDITION	nvarchar(100)	INOUT	Disability/Illness
@GENDER	nvarchar(20)	INOUT	Gender
@AGERANGE	nvarchar(50)	INOUT	Age range
@SOLESPONSORSHIP	nvarchar(4)	INOUT	Sole sponsorship
@LOCATION	nvarchar(255)	INOUT	Location

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIPOPPORTUNITYCHILDDETAILCRITERIA
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,

  @ISHIVPOSITIVE nvarchar(20)=null output,
    @ISORPHANED nvarchar(20)= null output,
    @CONDITION nvarchar(100)=null output,
  @GENDER nvarchar(20)= null output,
  @AGERANGE nvarchar(50) = null output,
  @SOLESPONSORSHIP nvarchar(4)=null output,
  @LOCATION nvarchar(255) = 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.

  declare @SPONSORSHIPOPPORTUNITYGROUPID uniqueidentifier = null
  declare @SPONSORSHIPOPPORTUNITYTYPECODE int = null
  declare @SPONSORSHIPOPPORTUNITYID uniqueidentifier = null

  select @SPONSORSHIPOPPORTUNITYID = SPONSORSHIPOPPORTUNITYID
  from dbo.SPONSORSHIP where ID=@ID

  select @SPONSORSHIPOPPORTUNITYGROUPID = SPONSORSHIPOPPORTUNITYGROUPID
  from dbo.SPONSORSHIPOPPORTUNITY where @SPONSORSHIPOPPORTUNITYID = ID

  select @SPONSORSHIPOPPORTUNITYTYPECODE = SPONSORSHIPOPPORTUNITYTYPECODE
  from dbo.SPONSORSHIPOPPORTUNITYGROUP where @SPONSORSHIPOPPORTUNITYGROUPID=ID

  select @LOCATION = coalesce(dbo.UFN_SPONSORSHIPLOCATION_GETFULLNAME(SPONSORSHIPLOCATIONID),'Greatest need')
  from SPONSORSHIP where @ID= ID

  if @SPONSORSHIPOPPORTUNITYTYPECODE = 1
  begin
    select 
       @DATALOADED = 1,
             @ISHIVPOSITIVE = ISHIVPOSITIVE,
             @CONDITION = HASCONDITION,
             @ISORPHANED = ISORPHANED,
             @GENDER = CHILDGENDER,
       @SOLESPONSORSHIP = 
          case ISSOLESPONSORSHIP
          when 0 then 'No'
          else 'Yes'
          end,
       @AGERANGE = coalesce(SPONSORSHIPOPPORTUNITYAGERANGE.DISPLAYNAME,'Greatest need')
        from dbo.SPONSORSHIP
    left join SPONSORSHIPOPPORTUNITYAGERANGE on SPONSORSHIPOPPORTUNITYAGERANGE.ID= SPONSORSHIP.SPONSORSHIPOPPORTUNITYAGERANGEID
       where SPONSORSHIP.ID = @ID
  end  

    return 0;