USP_HOUSEHOLDINFO_CREATEORUPDATE

Creates or updates the top row in the household info table.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@NAMEFORMATFUNCTIONID	uniqueidentifier	IN
@HOUSEHOLDRECOGNIZEHOUSEHOLD	bit	IN
@HOUSEHOLDRECOGNIZEMEMBERS	bit	IN
@MEMBERRECOGNIZEHOUSEHOLD	bit	IN
@MEMBERRECOGNIZEMEMBER	bit	IN
@MEMBERRECOGNIZEOTHERMEMBERS	bit	IN
@HOUSEHOLDSCANBEDONORS	bit	IN
@CHANGEAGENTID	uniqueidentifier	IN
@HOUSEHOLDREVENUERECOGNITIONTYPECODEID	uniqueidentifier	IN
@MEMBERREVENUERECOGNITIONTYPECODEID	uniqueidentifier	IN
Definition

Copy


            CREATE procedure dbo.USP_HOUSEHOLDINFO_CREATEORUPDATE (
                @ID uniqueidentifier = null,
                @NAMEFORMATFUNCTIONID uniqueidentifier = null,
                @HOUSEHOLDRECOGNIZEHOUSEHOLD bit = 0,
                @HOUSEHOLDRECOGNIZEMEMBERS bit = 0,
                @MEMBERRECOGNIZEHOUSEHOLD bit = 0,
                @MEMBERRECOGNIZEMEMBER bit = 0,
                @MEMBERRECOGNIZEOTHERMEMBERS bit = 0,
                @HOUSEHOLDSCANBEDONORS bit = 0,
                @CHANGEAGENTID uniqueidentifier = null,
                @HOUSEHOLDREVENUERECOGNITIONTYPECODEID uniqueidentifier = null,
                @MEMBERREVENUERECOGNITIONTYPECODEID uniqueidentifier = null
            ) as begin
                set nocount on;

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

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

                if exists(select 1 from dbo.HOUSEHOLDINFO) begin
                    select @ID = ID from dbo.HOUSEHOLDINFO order by DATEADDED;

                    update dbo.HOUSEHOLDINFO set
                        NAMEFORMATFUNCTIONID = @NAMEFORMATFUNCTIONID,
                        HOUSEHOLDRECOGNIZEHOUSEHOLD = @HOUSEHOLDRECOGNIZEHOUSEHOLD,
                        HOUSEHOLDRECOGNIZEMEMBERS = @HOUSEHOLDRECOGNIZEMEMBERS,
                        MEMBERRECOGNIZEHOUSEHOLD = @MEMBERRECOGNIZEHOUSEHOLD,
                        MEMBERRECOGNIZEMEMBER = @MEMBERRECOGNIZEMEMBER,
                        MEMBERRECOGNIZEOTHERMEMBERS = @MEMBERRECOGNIZEOTHERMEMBERS,
                        HOUSEHOLDSCANBEDONORS = @HOUSEHOLDSCANBEDONORS,
                        HOUSEHOLDREVENUERECOGNITIONTYPECODEID = @HOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                        MEMBERREVENUERECOGNITIONTYPECODEID = @MEMBERREVENUERECOGNITIONTYPECODEID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where
                        ID = @ID;
                end
                else begin
                    if @ID is null
                        set @ID = newid();

                    insert into dbo.HOUSEHOLDINFO (
                        NAMEFORMATFUNCTIONID,
                        HOUSEHOLDRECOGNIZEHOUSEHOLD,
                        HOUSEHOLDRECOGNIZEMEMBERS,
                        MEMBERRECOGNIZEHOUSEHOLD,
                        MEMBERRECOGNIZEMEMBER,
                        MEMBERRECOGNIZEOTHERMEMBERS,
                        HOUSEHOLDSCANBEDONORS,
                        HOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                        MEMBERREVENUERECOGNITIONTYPECODEID,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED
                    ) values (
                        @NAMEFORMATFUNCTIONID,
                        @HOUSEHOLDRECOGNIZEHOUSEHOLD,
                        @HOUSEHOLDRECOGNIZEMEMBERS,
                        @MEMBERRECOGNIZEHOUSEHOLD,
                        @MEMBERRECOGNIZEMEMBER,
                        @MEMBERRECOGNIZEOTHERMEMBERS,
                        @HOUSEHOLDSCANBEDONORS,
                        @HOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                        @MEMBERREVENUERECOGNITIONTYPECODEID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    )
                end

                return 0;
            end