For a front-end VFP application that is utilizing SQL Server, what is the best strategy for constructing a stored procedure where a history file is being queries for many accounts and many items?
For example, let's say I have an INVOICE table in SQL Server that has both an account and product column. A user, at run-time, may want to see a report on inovices for 20 accounts and 100 items....or maybe 10 accounts and 2 items, etc.
So the VFP app interface allows the user to select the 20 accts and 100 items...and let's say they're sitting in two VFP cursors called TMPACCTS and TMPITEMS. What is the most effective way to 'get' these two cursors into SQL server so that a stored procedure can do a join between INVOICE, TMPACCTS, and TMPITEMS?
Only way I've figured out is to create a temp table in SQL Server (#TMPACCTS and #TMPITEMS), use SQLEXEC to insert the 20 accts and 100 items that the user selected, and then run the stored proc between my INVOICE table and the two #TMP files. This 'works', but I don't know if that's a good practice, since hundreds of users could be doing the same thing.
There's probably some fundamental command or strategy I'm missing...
TIA,
Kevin