>>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')