The only time you absolutely need to use dynamic SQL is when, at compile time, you do not know the names of the database or the database objects against which your query will execute.

WHERE criteria do not count: As the author pointed out, there are ways to construct a WHERE clause to go this way or that depending upon whether a parameter value is missing.

It's the FROM clause that makes dynamic SQL essential. If, for example, I wish to write a procedure that performs a query against system tables, and I want at run time to specify which database I'm querying. The only way to turn the database name into a variable is through dynamic SQL. Or if your database contains two or more tables having the same or similar structure, and again you won't know until run-time which tables are to be queried... this is a job for dynamic SQL.