As you can see, the "why" question requires a different kind of answer, including historical rationale and underlying assumptions for the language, I'm not sure I can really do that justice.
This comprehensive article by SQL MVP Erland Sommarskog does attempt to provide some rationale, along with the mechanics:
The Curse and Blessings of Dynamic SQL:
Caching Query Plans
Every query you run in SQL Server requires a query plan. When you run a query the first time, SQL Server builds a query plan for it – or as the terminology goes – it compiles the query. SQL Server saves the plan in cache, and next time you run the query, the plan is reused.
This (and security, see below) is probably the biggest reason.
SQL operates under the premise that queries are not one-time operations, but that they will be used over and over. If the table (or the database!) is not actually specified in the query, it has no way to generate and save an execution plan for future use.
Yes, not every query we run will be re-used, but this is the default operating premise of SQL, so "exceptions" are meant to be exceptional.
A few other reasons Erland lists (note that he's explicitly listing advantages of using stored procedures, but many of these are also advantages of parameterized (non-dynamic) queries):
- The Permission System: the SQL engine can't predict whether you have the rights to run a query if it doesn't know the table (or database) you will be operating against. "Permission chains" using dynamic SQL is a pain in the butt.
- Reducing Network Traffic: Passing the name of the stored proc and a few parameter values over the network is shorter than a long query statement.
- Encapsulating Logic: You should be familiar with the advantages of encapsulating logic from other programming environments.
- Keeping Track of what Is Used: If I need to change a column definition, how can I find all the code that calls it? System procedures exists to find dependencies within a SQL database, but only if the code is in stored procedures.
- Ease of Writing SQL Code: Syntax check occurs when you create or modify a stored procedure, so hopefully fewer errors result.
- Addressing Bugs and Problems: A DBA can trace and measure the performance of individual stored procedures much more easily than ever-changing dynamic SQL.
Again, each of these has a hundred nuances that I won't get into here.