Sql server® 2012 t-sql fundamentals

 

CHAPTER 10

 

Programmable Objects

 

 359

Dynamic SQL

SQL Server allows you to construct a batch of T-SQL code as a character string and then execute that 

batch. This capability is called dynamic SQL. SQL Server provides two ways of executing dynamic SQL: 

using the EXEC (short for EXECUTE) command, and using the sp_executesql stored procedure. I will 

explain the difference between the two and provide examples for using each.

Dynamic SQL is useful for several purposes, including:

■

■

Automating administrative tasks  For example, querying metadata and constructing and 

executing a BACKUP DATABASE statement for each database in an on-premises instance

■

■

Improving performance of certain tasks  For example, constructing parameterized ad-hoc 

queries that can reuse previously cached execution plans (more on this later)

■

■