CELKO (1/4/2013)
3) Why would you have a design so bad that you need dynamic SQL? Are data element names changing at run time? Is the name of the procedure “No_Idea_What_I_Am Doing_Until_Run Time”? Or (more likely) are you doing display formatting the database layer and not the presentation layer?
He doesn't, no, no and no. (and there was nothing in the OP's question to suggest either of the latter two, nor is this a 'tight coupling' or 'loose cohesion' issue. This is a very common search pattern where users can enter any number of filters to search on (eg search by first name and/or last name and/or phone number and/or department).
The recommendation of dynamic SQL in this case is solely to overcome a shortcoming in the SQL query optimiser.
Did you even read the OP's question or did you just see the word 'dynamic' in the title and base your reply off that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability