People tend to be a little nervous around dynamic SQL. Often, though, it's the best way to solve the problem at hand. Sometimes it's even the only way.

The biggest area of concern is SQL injection. In this two-part series, I show ways to protect yourself (and no, it's not by sanitizing inputs). TL;DR: The most effective way is to use proper, strongly-typed parameters for things you can parameterize, QUOTENAME() around user-provided entity names (which can't be parameterized), and always assume that user input is a weapon.

• Protecting Yourself from SQL Injection – Part 1
• Protecting Yourself from SQL Injection – Part 2

Dynamic SQL is very handy for generating queries based on names of things that are unknown or constantly changing. A few select xamples:

• PIVOT without hard-coding column names
• UNPIVOT without hard-coding values
• Perform some task against all {object types} in a database
• Perform some task against all databases
• T-SQL Tuesday #155 : Do <X> to all the databases
• Search all objects in all databases (and all job steps)
• Drop and re-create all foreign key constraints

It can also be useful for ensuring that different plans are used for different combinations of optional parameter values. I show examples (including also conditionally adding OPTION (RECOMPILE) to deal with value skew in addition to parameter combinations):

• #BackToBasics: An Updated Kitchen Sink Example

It is a bit cumbersome to use dynamic SQL and then retrieve output parameters from within that dynamic SQL. This answer on Stack Overflow helps with the approach for that:

• Obtain output parameter values from dynamic SQL

Always use nvarchar strings (with the N prefix on all literals) for anything you append to your @sql variables, and always use sys.sp_executesql as opposed to EXEC() because the latter is so bad at promoting unsafe, injection-prone strings.

• Bad Habits to Kick : Using EXEC() instead of sp_executesql

No self-respecting reference on dynamic SQL could neglect Erland Sommarskog's treatments on the topic:

• The Curse and Blessings of Dynamic SQL
• Dynamic Search Conditions in T‑SQL

Finally, SSMS is not a great place to review a dynamic SQL string before executing it, because longer text is almost certainly going to be truncated at various points depending on version, output method, and your settings. This often causes people to believe that their command has lost text, but really it's just the rendering tool not showing the whole string. You can increase the limit for grid and text output, but this only gets you so far. I wrote this tip a while back to show ways you can view the whole command more reliably, though they're admittedly still imperfect:

• Validate the contents of large dynamic SQL strings