Before SQL Server executes a batch query, each statement in the batch is checked. If any of the statements have syntax errors, the batch will not be executed. This can be a problem if your query statement uses a variable to define a table, because SQL Server will search for an actual table by that name. When it does not find one, it will send back a syntax error. When you issue statements dynamically, the SQL statements are not evaluated until the batch is executed. By this time, the variable in your statement will be defined, and your query will run smoothly.

Dynamic SQL Statements consist of character-type variable strings that are concatenated together.
After you have built your SQL statement using variables, you will use the EXECUTE (or EXEC) function to execute the statement on SQL Server. Following is an example of using the EXEC function:

EXEC @tmp

This statement will send the Transact-SQL statement to the SQL Server query processor that was built into the @tmp variable. The syntax of the string stored into the @tmp variable is not known to the query processor before it is executed with the EXEC statement. Therefore, when the syntax of the batch is checked, it will not fail because it only checks the syntax of the EXEC statement. It only evaluates the syntax of the SELECT statement when that line of Transact-SQL is executed. This allows you to build your Transact-SQL statements to include objects that do not exist at the time the statement is created. The following Slide Show demonstrates how to construct a dynamic-sql-statement.php.

Dynamic Sql Statement
In the next lesson, the information covered in this module will be reviewed.