Quote Originally Posted by kleinma
ok so if I want to return a recordset from this SP (which would be data from more than 1 table), but I want to extensivly filter it also based on passed in params, would I create a temp table in the SP and filter in there? or can you use select statements and filter the result set down until its what you want and then return it?
Well - that depend...

It's so based on what criteria and index matches you have.

We like to usually have one big-old SELECT+JOINS that we WHERE clause with all the criteria.

If it behaves well, then we go with it.

If it does not then we are forced to consider either SUB-QUERY's, TEMP TABLES or TABLE VARIABLES.

TABLE VARIABLES are my favorite.

Code:
DECLARE @TBLVAR TABLE (COL1 int, COL2 varchar(10))
But they start behaving poorly with many rows - work better then TEMP TABLES with very few rows.

Sometimes we do lots if IF/BLOCKS to decide which query to execute...