USP_DATAFORMTEMPLATE_EDITLOAD_BIOGRAPHICALDEMOGRAPHIC

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@SOURCE	nvarchar(100)	INOUT
@FIRSTNAME	nvarchar(100)	INOUT
@MIDDLENAME	nvarchar(100)	INOUT
@LASTNAME	nvarchar(100)	INOUT
@SUFFIXCODEID	uniqueidentifier	INOUT
@TITLECODEID	uniqueidentifier	INOUT
@BIRTHDATE	UDT_FUZZYDATE	INOUT
@AGE	int	INOUT
@MARITALSTATUSCODEID	uniqueidentifier	INOUT
@OCCUPATION	nvarchar(100)	INOUT
@CNOTES	nvarchar(1024)	INOUT
@HOUSEHOLDMOSAIC	nvarchar(100)	INOUT
@HOUSEHOLDINCOMECODEID	uniqueidentifier	INOUT
@DISCRETIONARYSPENDING	money	INOUT
@HHMEMBER_FIRSTNAME	nvarchar(100)	INOUT
@HHMEMBER_MIDDLENAME	nvarchar(100)	INOUT
@HHMEMBER_LASTNAME	nvarchar(100)	INOUT
@HHMEMBER_SUFFIXCODEID	uniqueidentifier	INOUT
@HHMEMBER_TITLECODEID	uniqueidentifier	INOUT
@HHMEMBER_BIRTHDATE	UDT_FUZZYDATE	INOUT
@HHMEMBER_AGE	int	INOUT
@HHMEMBER_MARITALSTATUSCODEID	uniqueidentifier	INOUT
@HHMEMBER_OCCUPATION	nvarchar(100)	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_BIOGRAPHICALDEMOGRAPHIC
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
      @SOURCE nvarchar(100) = null output,
      @FIRSTNAME nvarchar(100) = null output,
    @MIDDLENAME nvarchar(100) = null output,
    @LASTNAME nvarchar(100) = null output,
      @SUFFIXCODEID uniqueidentifier = null output,
    @TITLECODEID uniqueidentifier = null output,
    @BIRTHDATE dbo.UDT_FUZZYDATE = null output,
    @AGE int = null output,
    @MARITALSTATUSCODEID uniqueidentifier = null output,
    @OCCUPATION nvarchar(100) = null output,
    @CNOTES nvarchar(1024) = null output,
    @HOUSEHOLDMOSAIC nvarchar(100) = null output,
    @HOUSEHOLDINCOMECODEID uniqueidentifier = null output,
    @DISCRETIONARYSPENDING money = null output,
    @HHMEMBER_FIRSTNAME nvarchar(100) = null output,
    @HHMEMBER_MIDDLENAME nvarchar(100) = null output,
    @HHMEMBER_LASTNAME nvarchar(100) = null output,
      @HHMEMBER_SUFFIXCODEID uniqueidentifier = null output,
    @HHMEMBER_TITLECODEID uniqueidentifier = null output,
    @HHMEMBER_BIRTHDATE dbo.UDT_FUZZYDATE = null output,
    @HHMEMBER_AGE int = null output,
    @HHMEMBER_MARITALSTATUSCODEID uniqueidentifier = null output,
    @HHMEMBER_OCCUPATION nvarchar(100) = null output
)
as

    set nocount on;

    -- be sure to set these, in case the select returns no rows
    set @DATALOADED = 0;
    set @TSLONG = 0;

  with HHMEMBER_CTE (WPBIOGRAPHICALDEMOGRAPHICID, 
                     FIRSTNAME, 
                     MIDDLENAME, 
                     LASTNAME, 
                     SUFFIXCODEID, 
                     TITLECODEID, 
                     BIRTHDATE, 
                     AGE, 
                     MARITALSTATUSCODEID, 
                     OCCUPATION)
  as
  (
    select top 1
      WPBIOGRAPHICALDEMOGRAPHICID, 
      FIRSTNAME, 
      MIDDLENAME, 
      LASTNAME, 
      SUFFIXCODEID, 
      TITLECODEID, 
      BIRTHDATE, 
      AGE, 
      MARITALSTATUSCODEID, 
      OCCUPATION
    from dbo.WPBIOGRAPHICALHHMEMBER
    where WPBIOGRAPHICALDEMOGRAPHICID = @ID
    order by LASTNAME asc, FIRSTNAME asc, AGE desc
  )
    select
        @DATALOADED = 1,
        @TSLONG = TSLONG,
        @SOURCE = BD.SOURCE,
        @FIRSTNAME = BD.FIRSTNAME,
        @MIDDLENAME = BD.MIDDLENAME,
    @LASTNAME = BD.LASTNAME,
    @SUFFIXCODEID = BD.SUFFIXCODEID,
    @TITLECODEID = BD.TITLECODEID,
    @BIRTHDATE = BD.BIRTHDATE,
    @AGE = BD.AGE,
    @MARITALSTATUSCODEID = BD.MARITALSTATUSCODEID,
    @OCCUPATION = BD.OCCUPATION,
    @CNOTES = BD.CNOTES,
    @HOUSEHOLDMOSAIC = BD.HOUSEHOLD_MOSAIC,
    @HOUSEHOLDINCOMECODEID = BD.HOUSEHOLD_INCOMECODEID,
    @DISCRETIONARYSPENDING = BD.DISCRETIONARY_SPEND,
    @HHMEMBER_FIRSTNAME = HHMEMBER_CTE.FIRSTNAME,
        @HHMEMBER_MIDDLENAME = HHMEMBER_CTE.MIDDLENAME,
    @HHMEMBER_LASTNAME = HHMEMBER_CTE.LASTNAME,
    @HHMEMBER_SUFFIXCODEID = HHMEMBER_CTE.SUFFIXCODEID,
    @HHMEMBER_TITLECODEID = HHMEMBER_CTE.TITLECODEID,
    @HHMEMBER_BIRTHDATE = HHMEMBER_CTE.BIRTHDATE,
    @HHMEMBER_AGE = HHMEMBER_CTE.AGE,
    @HHMEMBER_MARITALSTATUSCODEID = HHMEMBER_CTE.MARITALSTATUSCODEID,
    @HHMEMBER_OCCUPATION = HHMEMBER_CTE.OCCUPATION
    from dbo.WPBIOGRAPHICALDEMOGRAPHIC BD
  left join HHMEMBER_CTE on HHMEMBER_CTE.WPBIOGRAPHICALDEMOGRAPHICID = BD.ID
    where ID = @ID

    return 0;