I have a couple of questions about how to handle a basic/common scenario. Say you are developing a stored procedure that takes input parameters and then filters records in a table based on those inputs. For example, consider a table for Books with over 10 million rows in it. You create a stored procedure that takes the following inputs: @author, @title, @publish_date_start, @publish_date_end, @isbn, @price_min, @price_max. The parameters can pass a null value in which case you don't want to filter on that field/column. Also, once you've filtered the rows that match the input criteria, you want to not only return that result but also perform other SQL on that subset of data (such as a query that shows # books & total price by author using aggregate functions and GROUP BY). The typical approach I've seen to this problem is to create a temp table (e.g. #Books) and then declare a string that is used to create a T-SQL statement dynamically (including non-null parameters in a WHERE clause) to populate the temp table with the rows that match the input criteria. You can then select the entire result from the temp table and also then query the temp table as needed to perform any aggregate/summing or other additional operations. There are a few things I don't like about this approach and I'm looking for alternative ways to handle this scenario that improve performance. First, the result could include just a few rows or all rows, and if the result set is large (e.g. 99.9% of the table) then writing all those rows to a temp table (even if just the PK) can create performance/disk problems. Would a table variable be a better alternative or would this just create RAM problems? Is there another alternative, such a placing a virtual "marker" on rows that meet the input criteria, then running subsequent SQL on only "marked" rows? The closest I can think of would be using a temp table or table variable but only write PK values (not other columns) to minimize the size of these temp structures, but I'd like to know if there are other approaches I've not considered. Second, since the SQL statement for performing the initial filtering is being built dynamically in a varchar then executed, no compile-time execution plan can be established, etc. Are there ways to handle this type of search (including optional parameters) without using dynamically generated SQL strings to improve performance? Any thoughts?