This is a VERY common scenario - just saw it yet again at a client earlier this week. If you have many NULLable variables and/or tables that you join to just to check existence (i.e. you don't also pull data from them) dynamic SQL is BY FAR the way to go here. For those tables where the variable you are joining with are NULL, you can completely exclude a hit on that table! I routinely get 4-5 ORDERS OF MAGNITUDE performance gains from this type of refactor. IFs with nested sprocs is another option, but I feel it is more difficult to maintain personally. If you do use dynamic SQL please ensure you guard against SQL Injection.