USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIP

The load procedure used by the view dataform template "Membership 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.
@LEVEL	nvarchar(100)	INOUT	Level
@MEMBERSHIPID	nvarchar(120)	INOUT	Membership ID
@STATUS	nvarchar(9)	INOUT	Status
@EXPIRATIONDATE	datetime	INOUT	Expiration date
@JOINDATE	datetime	INOUT	Member since
@LASTRENEWEDON	datetime	INOUT	Last renewed
@NUMBERMEMBERS	smallint	INOUT	Members
@NUMBEROFCHILDREN	smallint	INOUT	Children
@TERM	nvarchar(8)	INOUT	Term
@ISPRIMARY	bit	INOUT	Primary
@COMMENTS	nvarchar(1000)	INOUT	Comments
@ISGIFT	bit	INOUT	Gift
@GIVENBYID	uniqueidentifier	INOUT	Given by ID
@GIVENBY	nvarchar(700)	INOUT	Given by
@SENDRENEWALS	nvarchar(50)	INOUT	Send renewal notice to

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIP
        (
        @ID uniqueidentifier,
        @DATALOADED bit=0 output,
        @LEVEL nvarchar(100)=null output,
        @MEMBERSHIPID nvarchar(120)=null output,
        @STATUS nvarchar(9)=null output,
        @EXPIRATIONDATE datetime= null output,
        @JOINDATE datetime=null output,
        @LASTRENEWEDON datetime= null output,
        @NUMBERMEMBERS smallint = null output,
        @NUMBEROFCHILDREN smallint = null output,
        @TERM nvarchar(8)=null output,
        @ISPRIMARY bit=null output,
        @COMMENTS nvarchar(1000) = null output,
        @ISGIFT bit=null output,
        @GIVENBYID uniqueidentifier = null output,
        @GIVENBY nvarchar(700) = null output,
        @SENDRENEWALS nvarchar(50)=null output
                )
        as 
        set nocount on;
        declare @TODAY datetime;
        set @TODAY = getdate();
        set @DATALOADED = 0;

                select 
            @DATALOADED = 1,
            @LEVEL=MEMBERSHIPLEVEL.NAME,
            @MEMBERSHIPID = MEMBERSHIP.LOOKUPID,
           @STATUS = case 
                        when MEMBERSHIP.STATUSCODE = 0 and MEMBERSHIP.EXPIRATIONDATE <  @TODAY then 'Lapsed'
                        else MEMBERSHIP.STATUS
                        end,
            @EXPIRATIONDATE=MEMBERSHIP.EXPIRATIONDATE,
            @JOINDATE=MEMBERSHIP.JOINDATE,
            @LASTRENEWEDON=MEMBERSHIP.LASTRENEWEDON,
            @NUMBERMEMBERS=dbo.UFN_CONSTITUENTMEMBERSHIP_GETCOUNT(@ID),
            @NUMBEROFCHILDREN=MEMBERSHIP.NUMBEROFCHILDREN,
            @TERM=LEVELTERM.TERM,
            @ISPRIMARY=MEMBER.ISPRIMARY,
            @COMMENTS=MEMBERSHIP.COMMENTS,
            @ISGIFT = MEMBERSHIP.ISGIFT,
            @GIVENBYID = MEMBERSHIP.GIVENBYID,
            @GIVENBY = coalesce(NF.NAME, N''),
            @SENDRENEWALS = MEMBERSHIP.SENDRENEWAL
        from dbo.MEMBERSHIP
        inner join dbo.MEMBERSHIPLEVEL as LEVEL on
                MEMBERSHIP.MEMBERSHIPLEVELID = LEVEL.ID
        inner join dbo.MEMBERSHIPLEVELTERM AS LEVELTERM on 
            MEMBERSHIP.MEMBERSHIPLEVELTERMID=LEVELTERM.ID
        inner join dbo.MEMBERSHIPLEVEL on
                  MEMBERSHIP.MEMBERSHIPLEVELID=MEMBERSHIPLEVEL.ID
        inner join MEMBER on
            MEMBER.MEMBERSHIPID=MEMBERSHIP.ID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBERSHIP.GIVENBYID) NF
        where
            MEMBER.ID=@ID;