USP_DATAFORMTEMPLATE_EDIT_BIOGRAPHICALDEMOGRAPHIC2

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@FIRSTNAME	nvarchar(100)	IN
@MIDDLENAME	nvarchar(100)	IN
@LASTNAME	nvarchar(100)	IN
@SUFFIXCODEID	uniqueidentifier	IN
@TITLECODEID	uniqueidentifier	IN
@BIRTHDATE	UDT_FUZZYDATE	IN
@MARITALSTATUSCODEID	uniqueidentifier	IN
@OCCUPATION	nvarchar(100)	IN
@CNOTES	nvarchar(1024)	IN
@HHMEMBERS	xml	IN

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_BIOGRAPHICALDEMOGRAPHIC2
(
      @ID uniqueidentifier,
      @CHANGEAGENTID uniqueidentifier = null,
      @FIRSTNAME nvarchar(100),
    @MIDDLENAME nvarchar(100),
    @LASTNAME nvarchar(100),
      @SUFFIXCODEID uniqueidentifier,
    @TITLECODEID uniqueidentifier,
    @BIRTHDATE dbo.UDT_FUZZYDATE,
    @MARITALSTATUSCODEID uniqueidentifier,
    @OCCUPATION nvarchar(100),
    @CNOTES nvarchar(1024),
    @HHMEMBERS xml
)
as

    set nocount on;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()


  begin try

    declare @FULLNAME nvarchar(500);
    declare @EMPTYGUID uniqueidentifier = '00000000-0000-0000-0000-000000000000';

      if not exists(select * from dbo.WPBIOGRAPHICALDEMOGRAPHIC
                    where ID = @ID
                        and @FIRSTNAME = FIRSTNAME
                        and @MIDDLENAME = MIDDLENAME
                        and @LASTNAME = LASTNAME
                        and COALESCE(@SUFFIXCODEID,@EMPTYGUID) = COALESCE(SUFFIXCODEID,@EMPTYGUID)
                        and COALESCE(@TITLECODEID,@EMPTYGUID) = COALESCE(TITLECODEID,@EMPTYGUID)
              )
      begin
          set @FULLNAME = 
              case @TITLECODEID when null then '' else dbo.UFN_TITLECODE_GETDESCRIPTION(@TITLECODEID) + ' ' end
              +
              case @FIRSTNAME when '' then '' else @FIRSTNAME + ' ' end 
              +
              case @MIDDLENAME when '' then '' else @MIDDLENAME + ' ' end
              +
              case @LASTNAME when '' then '' else @LASTNAME end
              +
              case @SUFFIXCODEID when null then '' else ' ' + dbo.UFN_SUFFIXCODE_GETDESCRIPTION(@SUFFIXCODEID) end
      end

    update dbo.WPBIOGRAPHICALDEMOGRAPHIC set
      FULLNAME = COALESCE(NULLIF(@FULLNAME,''),FULLNAME),
      FIRSTNAME = @FIRSTNAME,
          MIDDLENAME = @MIDDLENAME,
      LASTNAME = @LASTNAME,
      SUFFIXCODEID = @SUFFIXCODEID,
      TITLECODEID = @TITLECODEID,
      BIRTHDATE = @BIRTHDATE,
      MARITALSTATUSCODEID = @MARITALSTATUSCODEID,
      OCCUPATION = @OCCUPATION,
      CNOTES = @CNOTES,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    where ID = @ID;

    --Insert HHMembers
    exec dbo.USP_WPBIOGRAPHICAL_GETHHMEMBERS_UPDATEFROMXML @ID, @HHMEMBERS, @CHANGEAGENTID;

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;