• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Passing a key pair list to a proc to handle one call of updates?

Hello all.  This is what I need to do.  I need to pass a ID list and then a Values list to a proc and then run an update to each row where ID = ID update the value to a field I have.  How can I do this, can someone please provide an example with the two lists coming in and how I split it and do the updates?  Thanks all for any help
0
sbornstein2
Asked:
sbornstein2
  • 6
  • 6
1 Solution
 
sm394Commented:
you can do somthing like that

DECLARE @SearchIn XML
SET @SearchIn ='<ROOT><ID>1</ID><ID>2</ID></ROOT>'

 Update Table Where Ticket.ID IN (
                                                                    SELECT  ParamValues.ID.value('.','int')
                                                                     FROM @SearchIn.nodes('//ID') as ParamValues(ID)
                                                                   )
0
 
sm394Commented:
adjust the update statment and key value pairs according to yours
0
 
sbornstein2Author Commented:
sorry still confused how to handle that, I am passing to the parameters two lists such as:

@IDs - "1,3,4,6,8,9"
@Values - "12.9,445.3, 232.2,15.0,12.0, 16.0'

I need to say:

Update MyPercentages Set PercentAmount = @Values value
Where PercentID = @ID value
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sm394Commented:
this will do all you need is pass data in xml format  key val pairs .

DECLARE @SearchIn XML set @SearchIn='<Root>
      <Data key="1" val="100"></Data>
      <Data key="2" val="200"> </Data>
    </Root>'

;WITH TestCTE AS (
   SELECT D.element.value('@key', 'int') as [key],D.element.value('@val', 'int') as val
 FROM @SearchIn.nodes('/Root/Data') AS D(element)
)
update MyPercentages
 set PercentID=TestCTE.val
from MyPercentages inner join TestCTE on TestCTE.[key]=MyPercentages .PercentID
0
 
sm394Commented:

since your values are float also change this bit in above code

D.element.value('@val', 'float') as val

DECLARE @SearchIn XML set @SearchIn='<Root>
      <Data key="1" val="100"></Data>
      <Data key="2" val="200"> </Data>
    </Root>'
 
;WITH TestCTE AS (
   SELECT D.element.value('@key', 'int') as [key],D.element.value('@val', 'float') as val
 FROM @SearchIn.nodes('/Root/Data') AS D(element)
)
update MyPercentages
 set PercentID=TestCTE.val
from MyPercentages inner join TestCTE on TestCTE.[key]=MyPercentages .PercentID

Open in new window

0
 
sbornstein2Author Commented:
ok this actually will be awesome, so here is what I just figured out I need to do any help would be greatly appreciated.  So this gets a little crazy.  Ok I have a table with the following fields:

PartnerID int,
PartnershipID int,
TaxPeriodID int,
FundID int,
InvestmentID int,
LineID int,
Amount float    -- not going to be a percentage but still float amount.

So I will need to pass in a list of PartnerID's that will all be unique each value, PartnershipID will be the same all the time, TaxPeriodID will be the same number, FundID the same, InvestmentID unique list again like PartnerID, LineID unique list, Amount is the value.  So if all the fields but the Amount don't exist I need to INSERT first and if not UPDATE.  I wish I could add more points somehow for all your help with this.
0
 
sm394Commented:
>>So if all the fields but the Amount don't exist I need to INSERT first and if not UPDATE

when you refer to Amount  did you mean in table or XML string
0
 
sbornstein2Author Commented:
I will need to check first if there is a record in the table first that has all the field values other than the amount then I update otherwise i need to create a new record with all the values such as:

If exists (Select * from MyTable where PartnerID = @PartnerID and PartnershipID = @PartnershipID and TaxPeriodID = @TaxPeriodID and FundID = @FundID and InvestmentID = @InvestmentID

then Update

else Insert a new record for this.  Because the first time I run this none of the values will exist to update.
0
 
sbornstein2Author Commented:
actually I was able to slim it down a little to FundInvestmentPartnerID to take place of PartnerID, PartnershipID, TaxPeriodID, FundID, InvestmentID.  So that is the only key field with LineID and Amount.
0
 
sm394Commented:
below is the simpliest form of query you asked. all you need to do is just change the names of fields and add according to your need
>> PartnerID, PartnershipID, TaxPeriodID, FundID, InvestmentID
During this process if you need some detail let me know
--------------------------------------------
DECLARE @SearchIn XML set @SearchIn='<Root><Data key="100" val="1702"></Data></Root>'

declare @key int
declare @val float

;WITH TestCTE AS (
   SELECT D.element.value('@key', 'int') as [key],D.element.value('@val', 'float') as val
 FROM @SearchIn.nodes('/Root/Data') AS D(element)
)
select @key=TestCTE.[key] ,  @val=TestCTE.val from TestCTE
if exists( select * from YourTable where [key]=@key)
begin
;WITH TestCTE AS (
   SELECT D.element.value('@key', 'int') as [key],D.element.value('@val', 'float') as val
 FROM @SearchIn.nodes('/Root/Data') AS D(element)
)
update YourTable
 set val=TestCTE.val
from YourTable inner join TestCTE on TestCTE.[key]=YourTable.[Key]

end
else
begin
;WITH TestCTE AS (
   SELECT D.element.value('@key', 'int') as [key],D.element.value('@val', 'float') as val
 FROM @SearchIn.nodes('/Root/Data') AS D(element)
)
insert into YourTable ([key],val)
 select TestCTE.[key],TestCTE.val from TestCTE
end

0
 
sbornstein2Author Commented:
thanks so much for all your help this is perfect
0
 
sbornstein2Author Commented:
sorry one more problem.  So if I pass the following in:

exec spImportDataSummary '<Root><Data key1="1" key2="1" val="50.01"></Data><Data key1="1" key2="2" val="75.01"></Data><Data key1="1" key2="3" val="90.00"></Data></Root>'

it works fine and inserts the data.  If I pass it again it updates the data perfect as well, however if I do the following:

exec spImportDataSummary '<Root><Data key1="1" key2="1" val="50.01"></Data><Data key1="1" key2="2" val="75.01"></Data><Data key1="1" key2="4" val="90.00"></Data></Root>'

so I have a new value the INSERT throws a FK constraint problem but the data:
Cannot insert duplicate key in object

I am thinking it now is trying to insert all the data.  See it is possible that one row may exist and then there is a added new one I need to insert.  I see though where that could be a pain because then it would have to loop through each record correct?  Here is my proc code:
ALTER PROCEDURE spImportDataSummary(@xmlString xml) AS
 
declare @key1 int
declare @key2 int
declare @val float
 
;WITH XMLTable AS (
   SELECT D.element.value('@key1', 'int') as key1, D.element.value('@key2', 'int') as key2, 
   D.element.value('@val', 'float') as val
 FROM @xmlString.nodes('/Root/Data') AS D(element)
)
select @key1=XMLTable.key1, @key2=XMLTable.key2 ,@val=XMLTable.val from XMLTable
if exists( select * from K1SummaryAllocation where FundInvestmentPartnerID=@key1 and LineID=@key2)
begin
;WITH XMLTable AS (
   SELECT D.element.value('@key1', 'int') as key1, D.element.value('@key2', 'int') as key2,
   D.element.value('@val', 'float') as val
 FROM @xmlString.nodes('/Root/Data') AS D(element)
)
UPDATE K1SummaryAllocation
 SET Amount = XMLTable.val
FROM K1SummaryAllocation INNER JOIN XMLTable on XMLTable.Key1=K1SummaryAllocation.FundInvestmentPartnerID
     AND XMLTable.Key2=K1SummaryAllocation.LineID
end
else
begin
;WITH XMLTable AS (
   SELECT D.element.value('@key1', 'int') as key1, D.element.value('@key2', 'int') as key2,
   D.element.value('@val', 'float') as val
 FROM @xmlString.nodes('/Root/Data') AS D(element)
)
INSERT INTO K1SummaryAllocation (FundInvestmentPartnerID, LineID, Amount)
 SELECT XMLTable.key1, XMLTable.key2,XMLTable.val from XMLTable
end

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now