USP_CONSTITUENT_GETPHONESWITHSEASONAL_ADDFROMXML

Used to add a set of records defined by UFN_CONSTITUENT_GETPHONESWITHSEASONAL from the given xml string.
Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@XML	xml	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
Definition

Copy

                CREATE PROCEDURE [dbo].[USP_CONSTITUENT_GETPHONESWITHSEASONAL_ADDFROMXML] 
                (
                @CONSTITUENTID uniqueidentifier,
                @XML xml,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
                )

                as

                set nocount on;

                if @CHANGEAGENTID is null
                  exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

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

                -- build a temporary table containing the values from the XML
                declare @TempTbl table (
                   [COUNTRYID] uniqueidentifier,
                   [ENDTIME] char(4),
                   [ID] uniqueidentifier,
                   [INFOSOURCECODEID] uniqueidentifier,
                   [INFOSOURCECOMMENTS] nvarchar(256),
                   [ISPRIMARY] bit,
                   [NUMBER] nvarchar(100),
                   [PHONETYPECODEID] uniqueidentifier,
                   [SEASONALENDDATE] char(4),
                   [SEASONALSTARTDATE] char(4),
                   [SEQUENCE] int,
                   [STARTDATE] date,
                   [STARTTIME] char(4))

                insert into @TempTbl select 
                    [COUNTRYID],
                    [ENDTIME],
                    [ID],
                    [INFOSOURCECODEID],
                    [INFOSOURCECOMMENTS],
                    [ISPRIMARY],
                    [NUMBER],
                    [PHONETYPECODEID],
                    [SEASONALENDDATE],
                    [SEASONALSTARTDATE],
                    [SEQUENCE],
                    [STARTDATE],
                    [STARTTIME] 
                from dbo.UFN_CONSTITUENT_GETPHONESWITHSEASONAL_FROMITEMLISTXML(@XML)

                update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

                if @@Error <> 0
                  return 1;

                -- insert new items
                insert into [PHONE] 
                  ([CONSTITUENTID], 
                    [COUNTRYID],
                    [ENDTIME],
                    [ID],
                    [INFOSOURCECODEID],
                    [INFOSOURCECOMMENTS],
                    [ISPRIMARY],
                    [NUMBER],
                    [PHONETYPECODEID],
                    [SEASONALENDDATE],
                    [SEASONALSTARTDATE],
                    [SEQUENCE],
                    [STARTDATE],
                    [STARTTIME],                
                  ADDEDBYID, 
                  CHANGEDBYID, 
                  DATEADDED, 
                  DATECHANGED)
                select @CONSTITUENTID, 
                    [COUNTRYID],
                    [ENDTIME],
                    [ID],
                    [INFOSOURCECODEID],
                    [INFOSOURCECOMMENTS],
                    [ISPRIMARY],
                    [NUMBER],
                    [PHONETYPECODEID],
                    [SEASONALENDDATE],
                    [SEASONALSTARTDATE],
                    [SEQUENCE],
                    [STARTDATE],
                    [STARTTIME], 
                  @CHANGEAGENTID, 
                  @CHANGEAGENTID, 
                  @CHANGEDATE, 
                  @CHANGEDATE
                from @TempTbl as [temp]

                if @@Error <> 0
                  return 2;

                return 0;