USP_DATAFORMTEMPLATE_VIEW_HOUSEHOLDINFO

The load procedure used by the view dataform template "Household 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.
@ISHOUSEHOLD	bit	INOUT	Is household
@NAME	nvarchar(154)	INOUT	Name
@CURRENTMEMBERCOUNT	int	INOUT	Current member count
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_HOUSEHOLDINFO
                (
                    @ID uniqueidentifier,
                    @ISHOUSEHOLD bit = null output,
                    @NAME nvarchar(154) = null output,
                    @CURRENTMEMBERCOUNT int = null output,
                    @DATALOADED bit = 0 output
                )
                as
                    set nocount on

                    select
                        @DATALOADED = 1,
                        @NAME = C.NAME,
                        @ISHOUSEHOLD = case
                            when GD.GROUPTYPECODE = 0 then 1
                            else 0
                        end
                    from dbo.CONSTITUENT C
                    left join dbo.GROUPDATA GD on C.ID = GD.ID
                    where C.ID = @ID

                    declare @CURRENTDATEEARLIESTTIME date;
                    set @CURRENTDATEEARLIESTTIME = getdate();                                                       
                    select @CURRENTMEMBERCOUNT = count(GM.ID) 
                                from dbo.GROUPMEMBER as GM
                                left join dbo.GROUPMEMBERDATERANGE as GMDR on GM.ID = GMDR.GROUPMEMBERID
                                where ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
                                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) 
                                    or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
                                and GM.GROUPID = @ID