Hi Michael,
Asking where the SP is I didn't mean free tables, VFP dbc or backend server like Oracle, MSSQLserver database. I see it's on VFP (local).
That way your SP's wouldn't mean a performance gain to query backend but would be available to anyone accessing the DBC (keeping business in DBC itself).
Honestly I don't know a simple way to show SPs are a good idea especially when you don't have the backend server yet. If you had it you might show SPs on backend (not VFP DBC SPs, Oracle or MSSQLserver or anything like that) run complex queries at server and return back only the data. Previously in a discussion I defended the idea (and continue to defend) in cases it might be more time and resource consuming to do complex queries on server than getting simply a larger set(s) to process the complex query on client side with VFP.
Maybe at the moment what you need is COM objects. Your COM classes could keep the logic how to access to data, what to update etc and how to do it (using SPT, ADO, backend SP etc). That way you would have the flexibility to design interface separate. You could test,enhance data retrieveal/update separately, maybe today with SPT, tomorrow ADO,XML,CursorAdapter,XML* classes etc. (I know saying is easier than doing:()
Performance vs flexibility,scalibility and both. And it's harder to suggest as I saw VFP8. There are new classes to make it easier to connect and update backends like CursorAdapter and XML* classes. For example in my tests CursorAdapter with an ADO connection to SQL server was so fast that each time I did it I checked my grid's values against local tables !!! (was using Northwind from SQL server that has tables and sample records almost same as testdata.dbc) and my computer is below average nowadays (Athlon 650). Not only that they were fast I could easily get an hierarchical .NET dataset as XML and convert them to cursors (not done update part with this yet:)
OK getting away from SP on database :) SPs on database (server database) execute on server directly and return only the result dataset which might be much more smaller than the dataset(s) to do the same on client side hence reducing data amaount to travel. Plus SPs on backend (at least for SQLserver) are compiled and cached. SPT calls should be compiled (once via SQLPrepare() or with each SQLExec() call) adding overhead. So in theory SPs are faster. As always extreme samples can prove the reverse-lots of factors like CPU count, how much complex the SP code is, server's memory, how much data needs to travel back etc :) Kind of trade off.
Cetin

>Hi Cetin
>
>>Where is the SP ? On VFP database or backend ?
>
>It is a VFP database. '.dbc' not free tables. At the moment we use the VFP ODBC to access the data. Backend??? If you mean SQL Server Or Oracle database. Not yet. But If I can't prove that SPs are a good idea. We will not use then when we convert.
>
>>I assume from your post you don't currently have backend, SP is on VFP database and will simulate SQLExec()ing against another VFP table from another dbc (SP querying Testdata!customer as if it were a backend table) :
>
>I opened the database "MODI DATABASE". Edit the SPs by adding a new one. Compiled the database. Then tried to SQLEXEC() the SP.
>
>PROCEDURE get_proj_hrs
> LPARAMETERS pnPersNo, pdWeekAct
>
>SELECT PROJ_HRS.*, ;
> SPACE(1) AS ExtSite, ;
> .F. AS line_chng, ;
> .F. AS match_found, ;
> .F. AS new_line ;
> FROM PROJ_HRS ;
> WHERE INLIST(PROJ_HRS.proj_cat_no, 1, 6) ;
> AND PROJ_HRS.wrk_week = pdWeekAct ;
> AND PROJ_HRS.accepted = 0 ;
> AND PROJ_HRS.refused = 0 ;
> AND PROJ_HRS.atelier = 0 ;
> AND PROJ_HRS.wrk_hrs <> 0 ;
> AND PROJ_HRS.pers_no <> pnPersNo ;
> ORDER BY PROJ_HRS.pers_no