USP_DATAFORMTEMPLATE_EDIT_MERCHANDISEPRODUCTINSTANCE_4

                create procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MERCHANDISEPRODUCTINSTANCE_4 (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @SALEPRICE money,
                    @PRODUCTINSTANCEOPTIONS xml,
                    @LOOKUPCODE nvarchar(25),
                    @COST money,
                    @LOOKUPITEMS xml
                )
                as

                set nocount on;

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

                declare @CURRENTDATE datetime
                set @CURRENTDATE = getdate()

                begin try
                    -- handle updating the data
                    update dbo.MERCHANDISEPRODUCTINSTANCE 
                    set
                        SALEPRICE = @SALEPRICE,
                        LOOKUPCODE = @LOOKUPCODE,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @ID

                    update dbo.MERCHANDISEPRODUCTINSTANCEVENDOR 
                    set
                        COST=@COST
                    where MERCHANDISEPRODUCTINSTANCEID=@ID

                    declare @OPTIONTABLE table(
                        OPTIONID uniqueidentifier, 
                        OPTIONVALUEID uniqueidentifier,
                        PRODUCTINSTANCEOPTIONID uniqueidentifier
                    )

                    --Building table of selected option values
                    insert into @OPTIONTABLE
                    select
                        PRODUCTINSTANCEOPTIONS.OPTIONID,
                        PRODUCTINSTANCEOPTIONS.OPTIONVALUEID,
                        MERCHANDISEPRODUCTINSTANCEOPTIONVALUE.ID PRODUCTINSTANCEOPTIONID --Product instance's record for this option 
                    from dbo.MERCHANDISEPRODUCTINSTANCEOPTIONVALUE
                    inner join dbo.MERCHANDISEPRODUCTOPTIONVALUE on
                        MERCHANDISEPRODUCTINSTANCEOPTIONVALUE.MERCHANDISEPRODUCTOPTIONVALUEID = MERCHANDISEPRODUCTOPTIONVALUE.ID
                    inner join dbo.MERCHANDISEPRODUCTOPTION on
                        MERCHANDISEPRODUCTOPTIONVALUE.MERCHANDISEPRODUCTOPTIONID = MERCHANDISEPRODUCTOPTION.ID
                    inner join (
                        select
                            T.item.value('@OPTIONID','uniqueidentifier') as OPTIONID,
                            T.item.value('@OPTIONVALUEID','uniqueidentifier') as OPTIONVALUEID
                        from @PRODUCTINSTANCEOPTIONS.nodes('PRODUCTINSTANCEOPTIONS/ITEM') T(item)
                    ) as PRODUCTINSTANCEOPTIONS on
                        MERCHANDISEPRODUCTOPTION.ID = PRODUCTINSTANCEOPTIONS.OPTIONID
                    where MERCHANDISEPRODUCTINSTANCEOPTIONVALUE.MERCHANDISEPRODUCTINSTANCEID = @ID

                    --update existing product instance options
                    update dbo.MERCHANDISEPRODUCTINSTANCEOPTIONVALUE set
                        MERCHANDISEPRODUCTOPTIONVALUEID = [OPTIONS].OPTIONVALUEID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    from dbo.MERCHANDISEPRODUCTINSTANCEOPTIONVALUE 
                    inner join @OPTIONTABLE [OPTIONS] on
                        MERCHANDISEPRODUCTINSTANCEOPTIONVALUE.ID = [OPTIONS].PRODUCTINSTANCEOPTIONID

                    --update existing product instance lookup codes
                    exec dbo.USP_MERCHANDISEPRODUCTINSTANCE_GETLOOKUPITEMS_UPDATEFROMXML @ID, @LOOKUPITEMS, @CHANGEAGENTID

                    if dbo.UFN_MERCHANDISEPRODUCTINSTANCE_CHECKOPTIONCOUNT(@ID) = 0
                    begin
                        raiserror('ERR_MERCHANDISEPRODUCTINSTANCE_OPTIONCOUNTNOTCORRECT', 13, 1);
                    end

                    if dbo.UFN_MERCHANDISEPRODUCTINSTANCE_OPTIONSUNIQUE(@ID) = 0
                    begin
                        raiserror('ERR_MERCHANDISEPRODUCTINSTANCE_OPTIONSNOTUNIQUE', 13, 1);
                    end
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR
                    return 1
                end catch

                return 0;