Compare Page Revisions
« Older Revision - Back to Page History - Newer Revision »
@ResourceId
<x v="1"/><x v="13"/>...
PyramidId
... where 1=1 and @ResourcePyramids.exist('/x[@v = sql:column("PyramidId")]') = 1
/*-------------------------------------------------------------------------------- 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 )
with MyLyrics as ( select SongId ,TitleExtended ,CreditsExtended ,Lyric = T.c.value('.', 'varchar(MAX)') from SongExtended cross apply Lyrics.nodes('/song/section/line') T(c) where 1=1 and Lyrics is not null ) select SongId ,TitleExtended ,CreditsExtended ,Lyric from MyLyrics
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.