I have a stored procedure that is performing a lookup function that ends up accessing way too many tables ... 12 lets say for example (I normally like to keep a query to six tables or less).

In VFP I'd handle this buy breaking up the query into several different queries that place intermediate results into cursors. Then, I'd use the cursors to bring in the other resultant sets from the tables by joining cursors (that usually have quite small resultant sets) with the additional tables from which I need data.

This can be done with SQL Server using temporary tables and the # sign.

SELECT tb1.fl1 INTO #tTemp FROM tb1

My question is this: with SQL Server is this a best practice? Or does this slow SQL Server down by balling up the execution plan? How do people optimize a lookup query that has an ungodly number of tables?

Thanks,

CTBlankenship