USP_PRICELIST_UPDATEPROGRAMPRICE

        create procedure dbo.USP_PRICELIST_UPDATEPROGRAMPRICE
        (
            @ID uniqueidentifier = null,
            @PRICES xml = null,
            @PROGRAMS xml = null
        )
        as
        begin

            declare @CHANGEAGENTID uniqueidentifier

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

            declare @CURRENTDATE datetime
            set @CURRENTDATE = getdate()

            declare @contextCache varbinary(128);
            declare @e int;

            -- cache current context information 

            set @contextCache = CONTEXT_INFO();

            -- set CONTEXT_INFO to @CHANGEAGENTID 

            if not @CHANGEAGENTID is null
                set CONTEXT_INFO @CHANGEAGENTID;

            -- delete any prices currently connected to the program

            delete from dbo.[PROGRAMPRICE] 
            where 
                [PROGRAMPRICE].PROGRAMID in
                (
                    select T.c.value('(ID)[1]','uniqueidentifier') from
                    @PROGRAMS.nodes('/PROGRAMS/ITEM') T(c)
                )

            select @e=@@error;

            -- reset CONTEXT_INFO to previous value 

            if not @contextCache is null
                set CONTEXT_INFO @contextCache;

            if @e <> 0
                return 1;

            insert into dbo.[PROGRAMPRICE] 
            (
                [PROGRAMID], 
                [FACEPRICE],
                [ID],
                [PRICETYPECODEID],    
                [SEQUENCE],
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED
            )
            select 
                P.programs.value('(ID)[1]','uniqueidentifier'), 
                T.c.value('(FACEPRICE)[1]','money'),
                newid(),
                T.c.value('(PRICETYPECODEID)[1]','uniqueidentifier'),
                T.c.value('(SEQUENCE)[1]','integer'),
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE,
                @CURRENTDATE
            from @PRICES.nodes('/PRICES/ITEM') T(c)
            cross join 
                @PROGRAMS.nodes('/PROGRAMS/ITEM') P(programs)

            update dbo.PROGRAM set
                PRICELISTID = @ID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where 
                ID in
                (
                    select T.c.value('(ID)[1]','uniqueidentifier') from
                    @PROGRAMS.nodes('/PROGRAMS/ITEM') T(c)
                )
        end