>>>Hi,
>>>
>>>I have inherited a VFP system with a SQL Server 2012 backend.
>>>
>>>In the system a user can select multiple transactions that are to be assigned to a document. The IDs of the selected transactions are passed to a SQL Server stored procedure as a comma separated string. So a sample call might be like this:
>>>
>>>

DocumentID = "123"
>>>TransactionIDs = "1,2,3,4,5"
>>>
>>>SQLEXEC("myStoredProc " + m.DocumentID + ", " + m.TransactionIDs)

>>>
>>>
>>>The stored procedure:
>>>
>>>

ALTER procedure [dbo].[uspAssignTransactions] 
>>>	@DepositVoucherID int = null, 
>>>	@TransactionIDs varchar(4000) = null
>>>as
>>>
>>>select ID 
>>>into #IDs2Assign 
>>>from CaseTransactions 
>>>where charindex(',' + ltrim(rtrim(cast(ID as varchar(10)))) + ',', @TransactionIDs) > 0 and 
>>>	DepositVoucherID is null and ClearedForDeposit = 1
>>>
>>>update CaseTransactions set DepositVoucherID = @DepositVoucherID where ID in (select ID from #IDs2Assign)

>>>
>>>
>>>
>>>Now, comes the complication. They have multiple branches or locations each with its own database but they want to merge the databases, hence we'll end up with multiple transactions with the same ID. They do have a location code for each branch so each transaction can be identified by the location code and transaction ID.
>>>
>>>So, how do I adjust this to include the location code of the selected transaction as part of the call to the stored procedure and then adjust the stored procedure to use this location code and ID to uniquely identify the transactions?
>>
>>I suggest to pass the info as XML instead.
>
>and then use OPENXML()? Could you give me a quick example of how to use the XML in the update query?
>
>Something like this:
>
>

CREATE procedure [dbo].[uspAssignTransactions] 
>	@DepositVoucherID int = null, 
>	@xmlDocument xml
>as
>
>DECLARE @docHandle int;
>
>EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
>
>UPDATE CaseTransactions
>SET DepositVoucherID = @DepositVoucherID
>FROM OPENXML(@docHandle, N'/ROOT/Transactions') 
>
>-- here's where I am lost, how to join?
>

Frank,
XML would only be a (slower) way of passing the data to SQL server. Your issue is not with how to pass it, no? I couldn't understand your scenario, thus can't comment really. If it were all about passing the IDs then it is easy and done in many ways, some of which are extremely fast. If you think XML was the answer here is a sample:

LOCAL lnHandle, cXML, myInQuery

TEXT TO myInQuery noshow
DECLARE @hDoc int
DECLARE @tbl TABLE (pId int)

exec sp_xml_preparedocument @hDoc OUTPUT, ?m.cXML

INSERT @tbl SELECT * FROM OPENXML(@hDoc, ?m.cNodename, 1) WITH (myid int)

EXEC sp_xml_removedocument @hDoc

SELECT * FROM [Northwind]..[Products] WHERE ProductID IN (SELECT pID FROM @tbl)
ENDTEXT


** Local cursor
CREATE CURSOR test (myID i)
INSERT INTO test VALUES (1)
INSERT INTO test VALUES (3)
INSERT INTO test VALUES (5)
** Local cursor

CURSORTOXML('test','cXML',2)

cNodeName = '/VFPData/test' && CursorToXML by default use VFPData as root, and tablename in lowercase for rows

lnHandle = SQLSTRINGCONNECT('Driver={SQL Server Native Client 11.0};server=.\SQLExpress2012;Trusted_Connection=yes')
SQLEXEC(m.lnHandle, m.myInQuery, 'result')

SQLDISCONNECT(m.lnHandle)

SELECT result
BROWSE

PS: I don't know why even replied here, I visit here very rarely:) Likely I will miss your followup explanations.