>If I can write my stored procedure using derived tables instead of temp tables, should I do so? Or are temp tables o.k. if I want to keep my stored procs easy to read?
Use derived tables when you can. If you can't, use a Table variable (SQL Server 2000) before using an actual temporary table.
>Also, what are the pitfalls of using temp tables over derived tables?
Temp tables require a little more work on your part and on the part of SQL Server. Many times, the derived table and be imbedded in the query plan. There are also locking requirements as the table is created in tempdb.
Table variables behave like temporary tables but are created in memory.
Of course, depending on what you query is doing and the amount of data that your affecting, SQL Server can always decide that it needs a work table and create it in tempdb.
-Mike