All well and good, but a lot of applications will need to actually retreive data from the data base for each line - like codes, costs, discounts etc as each line is used - perhaps that is why a lot of apps hit the db each time a line is required?.
Hi Mike,
This article presents a solution that works with SQL Server 2000 specifically. Towards the end of this series, I have an article that presents the same with the XML data type of SQL Server 2005.
Thankx
Jacob
Hi mike,
I agree with you. There are many cases where you still need to hit the DB many times. But I suppose most of those scenarios can be avoided if we re-design the process a little bit. I have seen this a few times in the past.
Do you have a scenario in your mind where it is absolutely necessary to hit the DB for each item of an entry?
Hi Confucius247,
I agree with you. Actually this article is part of a series, which demonstrates more and more features with each installment. The purpose of the first installment is to present a very simple usage. The second part of this series does use a single XML parameter which contains the order header information as well as item details. It also demonstrates the use of XML attributes.
Other than using XML, have you tried generating a flat file to a shared folder that SQL Server can get to. Then use either OPENROWSET or BULK INSERT to load the data into a staging table where you can look up prices, validate data, etc. From there, you can move valid records into the final table and display errors on any other records left in the staging database.
By using a staging table, you can do bulk updates on the records. For example:
update <staging table> set
unitcost = i.unitcost,
...
from <staging table> s
join items i on s.itemID = i.itemID
where s.poID = @someID
