USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPLEVELBASICCMS

The load procedure used by the view dataform template "Microsite Membership Level View"

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(512)	INOUT	Level name
@URL	nvarchar(max)	INOUT	URL
@ISACTIVE	bit	INOUT	Active
@ISBASICCMS	bit	INOUT	Approved for website
@ISLIVE	bit	INOUT	Live on website
@DESCRIPTION	nvarchar(255)	INOUT	Description
@SITEPAGESID	int	INOUT	Site Page ID

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPLEVELBASICCMS
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @NAME nvarchar(512) = null output,
                    @URL nvarchar(max) = null output,
                    @ISACTIVE bit = null output, 
                    @ISBASICCMS bit = null output,
                    @ISLIVE bit = null output,
                    @DESCRIPTION nvarchar(255) = null output,
                    @SITEPAGESID int = null output
                )
                as
                begin
                    set nocount on;

                    set @DATALOADED = 0;

                    select
                        @DATALOADED = 1,
                        @NAME = MEMBERSHIPLEVEL.NAME,
                        @URL = case 
                            when [ISBASICCMS].[IS] = 1 then 
                                dbo.UFN_BBNC_URL() + 
                                (select top 1 VanityURL from dbo.VanityURL where PageID= MICROSITEPAGE.SITEPAGESID) + 
                                '?mpl=' + cast(MEMBERSHIPLEVEL.ID as nvarchar(36))
                            else '' 
                        end, 
                        @ISACTIVE = MEMBERSHIPLEVEL.ISACTIVE,
                        @ISBASICCMS = [ISBASICCMS].[IS],
                        @ISLIVE = case
                            when [ISBASICCMS].[IS] = 1 and [MEMBERSHIPPROGRAM].[ISACTIVE] = 1 and [MEMBERSHIPLEVEL].[ISACTIVE] = 1 then 1 
                            else 0 
                        end, 
                        @DESCRIPTION = [MEMBERSHIPPROGRAM].[DESCRIPTION],
                        @SITEPAGESID = [MICROSITEPAGE].[SITEPAGESID]
                    from dbo.MEMBERSHIPLEVEL
                    inner join dbo.MEMBERSHIPPROGRAM
                        on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                    left join dbo.MICROSITEPAGE
                        on (MICROSITEPAGE.OBJECTID = MEMBERSHIPPROGRAM.ID) and (MICROSITEPAGE.EXCLUDED = 0)
                    left join dbo.MICROSITEMEMBERSHIPLEVEL
                        on MEMBERSHIPLEVEL.ID = MICROSITEMEMBERSHIPLEVEL.ID
                    cross apply (
                        select case 
                            when isnull(cast(MICROSITEPAGE.SITEPAGESID as bit), 0) = 1 and MICROSITEMEMBERSHIPLEVEL.ID is not null then 1 
                            else 0
                        end as [IS]
                    ) [ISBASICCMS]
                    where MEMBERSHIPLEVEL.ID = @ID;        

                    return 0;
                end