>Someone else responded that the VFP front-end could append to a temporary table in SQL Server with the list of selected accounts/items, just prior to calling the stored procedure. That seems like it would make sense, although there could be dozens of users running reports at the same time, so I'd need to figure out how to build a temp table that's specific to each user, and have the SP know which temp table to use.
>
>(It's probably obvious from my questions that I'm really struggling with the paradigm shift!)

Kevin,

I forgot to mention that creating a temporary table requires a certain setting on the database. I don't think it's a problem, and I have set up my database this way. Anyway, SQLServer will tell you what the setting is (I don't remember on the top of my head) if it's not set.

Now, about the multi-user issue. When creating a temporary table, give it a name that begins with the # sign, as in CREATE TABLE #AccountNoList ... The # is there to tell SQL we want a temporary table. The multi-user issue does not appear since each user has his own temporary table and does not see the other's. In fact, temporary tables created this way are for the connection, so if you were to open another connection on the same PC, that new connection would not see the table.

So, in recap, you could have something like this (this is how I currently work):

VFP:

&& Create the temp table on SQLServer
SQLEXEC(nConn, [CREATE TABLE #LstAccount (AccountNo INTEGER)])
SELECT <i>CursorThatContainsTheAccountNumbers</i>
SCAN
    SQLEXEC(nConn, [INSERT INTO #LstAccount (AccountNo) VALUES ] + ;
        [(?<i>CursorThatContainsTheAccountNumbers</i>.AccountNo)]
ENDSCAN

&& Execute the stored proc
SQLEXEC(nConn, [EXEC <i>StoredProcName</i>], "MyResult")

&& Delete the temp table on SQLServer
SQLEXEC(nConn, [DROP TABLE #LstAccount])

(Of course, I have more error handling than that)
My stored proc, on SQLServer, goes something like this:

SELECT Accounts.*
FROM #LstAccount
    INNER JOIN Accounts
        ON #LstAccount.AccountNo = Accounts.AccountNo
ORDER BY Accounts.AccountNo

Alternative:
If, however, you would rather not have temporary tables (maybe because for some reason you can't change the setting I was talking about), another method would be to have a permanent table. This, however, does bring in the multi-user issue. What I would suggest is to add another field that will contain the Process ID of the connection. SQLServer makes it available via the @@SPID variable, so your insert statement could be something like:

INSERT INTO ... (AccountNo, MySPID) VALUES (..., @@SPID)

Or better yet, put a default value of (@@SPID) when creating the table and just forget about it in the INSERT INTO. Next, in the Stored Procedure, just add this in the WHERE clause:

WHERE LstAccount.MySPID = @@SPID

I hope I've explained well enough! :)