USP_INDIVIDUAL_EDITSAVE_2

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@LASTNAME	nvarchar(100)	IN
@FIRSTNAME	nvarchar(50)	IN
@MIDDLENAME	nvarchar(50)	IN
@MAIDENNAME	nvarchar(100)	IN
@NICKNAME	nvarchar(50)	IN
@TITLECODEID	uniqueidentifier	IN
@SUFFIXCODEID	uniqueidentifier	IN
@GENDERCODE	tinyint	IN
@BIRTHDATE	UDT_FUZZYDATE	IN
@GIVESANONYMOUSLY	bit	IN
@PICTURE	varbinary	IN
@PICTURETHUMBNAIL	varbinary	IN
@PICTURECHANGED	bit	IN
@WEBADDRESS	UDT_WEBADDRESS	IN
@MARITALSTATUSCODEID	uniqueidentifier	IN
@TITLE2CODEID	uniqueidentifier	IN
@SUFFIX2CODEID	uniqueidentifier	IN
@DECEASED	bit	IN
@DECEASEDDATE	UDT_FUZZYDATE	IN
@GENDERCODEID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_INDIVIDUAL_EDITSAVE_2
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @LASTNAME nvarchar(100),
    @FIRSTNAME nvarchar(50),
    @MIDDLENAME nvarchar(50),
      @MAIDENNAME nvarchar(100),
      @NICKNAME nvarchar(50),
    @TITLECODEID uniqueidentifier,
    @SUFFIXCODEID uniqueidentifier,
    @GENDERCODE tinyint,
    @BIRTHDATE dbo.UDT_FUZZYDATE,
    @GIVESANONYMOUSLY bit,
    @PICTURE varbinary(max),
      @PICTURETHUMBNAIL varbinary(max),
      @PICTURECHANGED bit,
      @WEBADDRESS dbo.UDT_WEBADDRESS,
      @MARITALSTATUSCODEID uniqueidentifier,
      @TITLE2CODEID uniqueidentifier,
      @SUFFIX2CODEID uniqueidentifier,
    @DECEASED bit,
    @DECEASEDDATE dbo.UDT_FUZZYDATE,
    @GENDERCODEID uniqueidentifier = null
) as
    set nocount on;

    declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();

    declare @MAIDENNAMECHANGED bit = 0;

    select @MAIDENNAMECHANGED = case when @MAIDENNAME = CONSTITUENT.MAIDENNAME then 0 else 1 end
    from dbo.CONSTITUENT
    where CONSTITUENT.ID = @ID;

    begin try
        if @PICTURECHANGED = 1
            update 
                dbo.[CONSTITUENT]
            set
                [KEYNAME] = @LASTNAME,
                [FIRSTNAME] = @FIRSTNAME,
                [MIDDLENAME] = @MIDDLENAME,
                [MAIDENNAME] = @MAIDENNAME,
                [NICKNAME] = @NICKNAME,
                [TITLECODEID] = @TITLECODEID,
                [SUFFIXCODEID] = @SUFFIXCODEID,
                [ISORGANIZATION] = 0,
                [GENDERCODE] = @GENDERCODE,
                [BIRTHDATE] = @BIRTHDATE,
                [GIVESANONYMOUSLY] = @GIVESANONYMOUSLY,
                [PICTURE] = @PICTURE,
                [PICTURETHUMBNAIL] = @PICTURETHUMBNAIL,
                [WEBADDRESS] = @WEBADDRESS,
                [MARITALSTATUSCODEID] = @MARITALSTATUSCODEID,
                [TITLE2CODEID] = @TITLE2CODEID,
                [SUFFIX2CODEID] = @SUFFIX2CODEID,
                [DATECHANGED] = @CURRENTDATE,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [GENDERCODEID] = @GENDERCODEID
            where 
                [CONSTITUENT].ID = @ID;

        else
            update 
                dbo.[CONSTITUENT] 
            set
                [KEYNAME] = @LASTNAME,
                [FIRSTNAME] = @FIRSTNAME,
                [MIDDLENAME] = @MIDDLENAME,
                [MAIDENNAME] = @MAIDENNAME,
                [NICKNAME] = @NICKNAME,
                [TITLECODEID] = @TITLECODEID,
                [SUFFIXCODEID] = @SUFFIXCODEID,
                [ISORGANIZATION] = 0,
                [GENDERCODE] = @GENDERCODE,
                [BIRTHDATE] = @BIRTHDATE,
                [WEBADDRESS] = @WEBADDRESS,
                [GIVESANONYMOUSLY] = @GIVESANONYMOUSLY,
                [MARITALSTATUSCODEID] = @MARITALSTATUSCODEID,
                [TITLE2CODEID] = @TITLE2CODEID,
                [SUFFIX2CODEID] = @SUFFIX2CODEID,
                [DATECHANGED] = @CURRENTDATE,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [GENDERCODEID] = @GENDERCODEID
            where 
                [CONSTITUENT].ID = @ID;

      update 
        dbo.[DECEASEDCONSTITUENT]
      set 
        [DECEASEDDATE] = @DECEASEDDATE,
        [DATECHANGED] = @CURRENTDATE,
                [CHANGEDBYID] = @CHANGEAGENTID
      where 
        @DECEASED = 1 and 
        [ID] = @ID;

        if @MAIDENNAMECHANGED = 1 and @MAIDENNAME is not null and @MAIDENNAME != ''
        begin
            if not exists (select ID from dbo.ALIAS
                       where ALIAS.CONSTITUENTID = @ID
                       and ALIAS.KEYNAME = @MAIDENNAME
                       and ALIAS.FIRSTNAME = @FIRSTNAME
                       and ALIAS.MIDDLENAME = @MIDDLENAME)
                insert into dbo.ALIAS
                    (CONSTITUENTID, KEYNAME, FIRSTNAME, MIDDLENAME, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                values
                    (@ID, @MAIDENNAME, @FIRSTNAME, @MIDDLENAME, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
        end
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;