A stored procedure is pre-compiled (the query plan is generated when you run it the first time). If you change the conditional portion of your stored procedure, it will compile it again. In short, a "dynamic stored procedure" would be no better than a pass-thru query (with some exceptions, notably permissions, triggers and remote execution from a local server)...

What it sounds like you need to do is create a passthru query (if the where condition changes) or only pass in the variables that change (if your stored prcedure always searches for lastname and firstname, then you want to make the variables that represent lastname and firstname, parameters).