Erland's article, "The Curse and Blessings of Dynamic SQL" (the link that OTF inlcuded) is the best I have ever read about DSQL.
Itzek Ben Gan's Microsoft® SQL Server 2012 T-SQL Fundamentals* includes a good summary of when Dynamic SQL can be helpful:
Dynamic SQL is useful for several purposes, including:
■ Automating administrative tasks For example, querying metadata and constructing and executing a BACKUP DATABASE statement for each database in an on-premises instance
■ Improving performance of certain tasks For example, constructing parameterized ad-hoc queries that can reuse previously cached execution plans...
■ Constructing elements of the code based on querying the actual data For example, constructing a PIVOT query dynamically when you don’t know ahead of time which elements should appear in the IN clause of the PIVOT operator
* I know we are in the 2008 forum but all of the above is relevant to 2008 as well.
-- Alan Burstein
Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K
. "I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001