>It is much, much quicker to create a stored procedure per report and put the joins in there. One thing you shouldn't ever do is make a stored procedure multi-use. For example, say you want a list of clients using different search criteria.
>
>Don't do this
>
>create procedure dbo.findclient @option nchar(1),@term nvarchar(30) AS
>
>IF @option='A'
>BEGIN
>END
>IF @option='B'
>BEGIN
>END
>
>etc
>
>The reason is that SQL will create an execution plan for the whole procedure based on the best combination of indexes available the first time the proc is called. So if you call findclient with 'A' and this does a table scan, then call it with 'B' which would benefit from an index key, then it won't get used...
>The other thing you want to avoid is recompiles. These have an overhead on SQL which can be significant on an operational system.
>
>It is far more efficient to do all of the JOINs on SQL Server than it is to download data into VFP and then try to do the SET RELATION TO method. The less data that get's transferred over the network the better.

Simon,
I agree in general. For parametric one a udf might be better.
Bringing tables into VFP and setting relation might sometimes be better than doing the join and bringing in the result because it might mean to bring less data down the wire (plus it wouldn't need any special optimization on the server).
Cetin