SQL Server Video Creating Dynamic SQL Commands

By:   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | More > 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux 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 Back To Top (34774)

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 Back To Top (34772)

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