USP_BBNC_COMMITEVENTREGISTRATIONGUESTUPDATE_1_1

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@FIRSTNAME	nvarchar(50)	IN
@MIDDLENAME	nvarchar(50)	IN
@KEYNAME	nvarchar(100)	IN
@CONSTITUENCYCODEID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
@TITLECODEID	uniqueidentifier	IN
@SUFFIXCODEID	uniqueidentifier	IN
@MAIDENNAME	nvarchar(50)	IN
Definition

Copy


CREATE procedure dbo.USP_BBNC_COMMITEVENTREGISTRATIONGUESTUPDATE_1_1
(
                @ID uniqueidentifier = null,
                @FIRSTNAME nvarchar(50) = '',
                @MIDDLENAME nvarchar(50) = '',
                @KEYNAME nvarchar(100),
                @CONSTITUENCYCODEID uniqueidentifier = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @TITLECODEID as uniqueidentifier = null,
                @SUFFIXCODEID as uniqueidentifier = null,
                @MAIDENNAME nvarchar(50) = ''
            )
            as
                set nocount on;

                if @ID is null
                    begin
                        raiserror('The constituent ID is required',16,1);
                        return -2;
                    end

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

                if @CHANGEDATE is null
                    set @CHANGEDATE = getdate();

                --Used for adding constituency, not for DATEADDED/DATECHANGED

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

                begin try
                    update dbo.CONSTITUENT set
                        [FIRSTNAME] = @FIRSTNAME,
                        [MIDDLENAME] = @MIDDLENAME,
                        [KEYNAME] = @KEYNAME,
                        [CHANGEDBYID] = @CHANGEAGENTID,
                        [DATECHANGED] = @CHANGEDATE,
                        [TITLECODEID] = @TITLECODEID,
                        [SUFFIXCODEID] = @SUFFIXCODEID,
                        [MAIDENNAME] = @MAIDENNAME
                    where
                        [ID] = @ID;

                    if @CONSTITUENCYCODEID is not null
                    and not exists
                    (
                        select
                            1
                        from
                            dbo.CONSTITUENCY
                        where
                            [CONSTITUENTID] = @ID
                            and [CONSTITUENCYCODEID] = @CONSTITUENCYCODEID
                            and 
                            (
                                ([DATEFROM] is null and [DATETO] is null)
                                or ([DATEFROM] <= dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE) and [DATETO] is null)
                                or ([DATEFROM] is null and dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE) <= [DATETO])
                                or (dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE) between [DATEFROM] and [DATETO])
                            )
                    )
                        begin
                            declare @CONSTITUENCYDATETO datetime;

                            select top (1)
                                @CONSTITUENCYDATETO = dateadd(day, -1, [CONSTITUENCY].[DATEFROM])
                            from
                                dbo.CONSTITUENCY
                            where
                                [CONSTITUENCY].[CONSTITUENTID] = @ID
                                and [CONSTITUENCY].[CONSTITUENCYCODEID] = @CONSTITUENCYCODEID
                                and [CONSTITUENCY].[DATEFROM] > dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)
                            order by
                                [DATETO];

                            insert into dbo.CONSTITUENCY
                            (
                                [CONSTITUENTID],
                                [CONSTITUENCYCODEID],
                                [DATEFROM],
                                [DATETO],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED]
                            )
                            values
                            (
                                @ID,
                                @CONSTITUENCYCODEID,
                                dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE),
                                dbo.UFN_DATE_GETEARLIESTTIME(@CONSTITUENCYDATETO),
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CHANGEDATE,
                                @CHANGEDATE
                            );
                        end
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;