 <p>Stored procedures are not very good at being super-generic because it prevents SQL Server from always using optimal methods. In a similar situation recently I used (<em>gasp</em>) dynamic SQL. My search stored procedures would build the SQL code to perform the search, using pagination just like you have it (WITH with a ROW_NUMBER(), etc.). The advantage was that if parameters indicated that one piece of information wasn't being used in the search then the generated code would omit it. In the end it allowed for better query plans.</p> <p>Make sure that you use sp_executesql properly to prevent SQL injection attacks.</p>