Hi, all
I have a VFP app that makes a call to a stored procedure in SQL Server 2000. The stored procedure is basically a query against some invoice tables.
My question is this: while the JOIN clauses are all pretty much static at run-time, the field list in the SELECT, and the GROUP BY clause, are both variable.
I know how to code T-SQL statements so that I can pass the field list and the GROUP BY as parameters, and then construct that part of the syntax on the fly in the stored procedure. However, I do wonder if I'm losing some of the benefit of a stored proc, since some of the SQL statement is being evaluated at run-time and can't be compiled into an execution plan.
There are only four possibilities for my SELECT and GROUP BY, so I actually considered passing the 'scenario number' to the stored proc, and do like the following...
IF (case 1)
select fieldlist from tables join
group by
if (case 2)
select fieldlist from tables join group by
etc. etc.
This certainly isn't very elegant, but at least I wouldn't be evaluating as much at runtime.
So basically I'm looking for some feedback on what areas of a stored proc can you evaluate at runtime and not lose performance, or if there's a better way.
Thanks,
Kevin
