SQL Server Video Creating Dynamic SQL Commands

By: Greg Robidoux | Comments (2) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Dynamic SQL

Problem / Solution

Having the ability to write dynamic SQL statements is sometimes the only option besides having to write several versions of a SQL statement to cover all possible scenarios. In this video tip we look at a few different simple examples of how you can create SQL statements that are dynamic in nature. Note that dynamic SQL statements should be your last option, but in some cases this may be the only option.

Key Learning Items

Building dynamic T-SQL statements
Using SQL Server variables
Using EXEC to execute dynamic SQL
Using sp_executesql to execute dynamic SQL

Read Companion Tip

Click Here

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, September 30, 2014 - 9:41:48 AM - Greg Robidoux

Hi Ken,

In example 3 the parameter @city just needs to hold the value, we don't need to pass the single quotes into the command. This parameter value is then passed in the sp_executesql command. Since we don't need to have the value quoted as we did in example 2, we just need to set the parameter value without additional single quotes.

-Greg

Tuesday, September 30, 2014 - 7:27:22 AM - Ken Uber

You didn't explain why the @city variable did not require the triple quote for sp_executesql as required for exec.