SQL Server Video Creating Dynamic SQL Commands


By:   |   Updated: 2014-07-01   |   Comments (2)   |   Related: More > Dynamic SQL




 






Enter your business email address to
get free SQL Server tips.

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

Last Updated: 2014-07-01


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

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.

 

 

 

 

 

 



download





Recommended Reading

Execute Dynamic SQL commands in SQL Server

Using the CASE expression instead of dynamic SQL in SQL Server

Run a Dynamic Query against SQL Server without Dynamic SQL

Dynamic SQL execution on remote SQL Server using EXEC AT

Validate the contents of large dynamic SQL strings in SQL Server








get free sql tips
agree to terms


Learn more about SQL Server tools