USP_DATAFORMTEMPLATE_VIEW_DEMOGRAPHIC

The load procedure used by the view dataform template "Demographic 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.
@CONSTITUENTTYPE	tinyint	INOUT	Constituent type
@ETHNICITY	nvarchar(512)	INOUT	Ethnicity
@RELIGION	nvarchar(100)	INOUT	Religion
@TARGET	nvarchar(100)	INOUT	Target
@INCOME	nvarchar(100)	INOUT	Income
@BIRTHPLACE	nvarchar(50)	INOUT	Birthplace

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DEMOGRAPHIC(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,        
    @CONSTITUENTTYPE tinyint = null output,
    @ETHNICITY nvarchar(512) = null output,
    @RELIGION nvarchar(100) = null output,
    @TARGET nvarchar(100) = null output,
    @INCOME nvarchar(100) = null output,
    @BIRTHPLACE nvarchar(50) = null output
)
    as
        set nocount on;

        set @DATALOADED = 0;

        select 
            @DATALOADED = 1,
            @CONSTITUENTTYPE = case 
                when (C.ISORGANIZATION = 0) and (C.ISGROUP = 0) then 0    -- Individual

                when (C.ISORGANIZATION = 1) then 1                        -- Organization

                when (GD.GROUPTYPECODE = 0) then 2                        -- Household

                else 3 end,                                                -- Group


            @ETHNICITY = (select stuff((select '; ' + ETHNICITYCODE.DESCRIPTION
                                        from 
                                            dbo.DEMOGRAPHICETHNICITY
                                            join dbo.ETHNICITYCODE on ETHNICITYCODE.ID = DEMOGRAPHICETHNICITY.ETHNICITYCODEID
                                        where 
                                            DEMOGRAPHICETHNICITY.DEMOGRAPHICID = @ID
                                        order by ETHNICITYCODE.DESCRIPTION
                          for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')),

            @RELIGION = dbo.UFN_RELIGIONCODE_GETDESCRIPTION(D.RELIGIONCODEID),
            @TARGET = dbo.UFN_TARGETCODE_GETDESCRIPTION(D.TARGETCODEID),
            @INCOME = dbo.UFN_INCOMECODE_GETDESCRIPTION(D.INCOMECODEID),
            @BIRTHPLACE = D.BIRTHPLACE
        from dbo.CONSTITUENT C
            left outer join dbo.DEMOGRAPHIC D with (nolock) on D.ID = C.ID
            left outer join dbo.GROUPDATA GD with (nolock) on GD.ID = C.ID
        where 
            C.ID = @ID 

        return 0;