...

where 1=1
    and @ResourcePyramids.exist('/x[@v = sql:column("PyramidId")]') = 1

To Submit a Table of Data to a Stored Procedure¶

/*--------------------------------------------------------------------------------
Node Names for the @dataSet input parameter.  
Root Node = <NewDataSet>
      RplProjectResourceRole: GridRow, RplProjectResourceRoleId, RplProjectId, 
                              RoleId, ResourceId
      PlannedAllocation: GridRow, RplProjectResourceRoleId, AmcYear, AmcMonth, 
                         AllocationPct
--------------------------------------------------------------------------------*/
;with InputDataSet as (
    select
        RplProjectResourceRoleId = T.c.value('RplProjectResourceRoleId[1]','int'),
        AmcYear                  = T.c.value('AmcYear[1]','int'),
        AmcMonth                 = T.c.value('AmcMonth[1]','int'),
        AllocationPct            = T.c.value('AllocationPct[1]','decimal(8,2)')
    from 
        @dataSet.nodes('/NewDataSet/PlannedAllocation') T(c)
    )
update
    PlannedAllocation
set
    AllocationPct = (
        select 
            AllocationPct
        from 
            InputDataSet
        where 1=1
            and RplProjectResourceRoleId = PlannedAllocation.RplProjectResourceRoleId
            and AmcYear                  = PlannedAllocation.AmcYear
            and AmcMonth                 = PlannedAllocation.AmcMonth
            and @planTypeId              = PlannedAllocation.PlanTypeId
        ),
    LastUpdatedBy = @userid,
    LastUpdatedOn = getdate()
WHERE 1=1
    AND EXISTS (
        select 
            2 
        from 
            InputDataSet
        where 1=1
            and RplProjectResourceRoleId = PlannedAllocation.RplProjectResourceRoleId
            and AmcYear                  = PlannedAllocation.AmcYear
            and AmcMonth                 = PlannedAllocation.AmcMonth
            and @planTypeId              = PlannedAllocation.PlanTypeId
        )