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
sbornstein2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.