Dynamic SQL
Dynamic SQL

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.

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

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):

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:

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.

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

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:

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.