Lesson 6 | Constructing SQL statements dynamically |
Objective | Describe how to construct SQL statements dynamically. |
Constructing SQL Statements dynamically
Why construct statements dynamically?
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.
Building a Dynamic Sql Statement
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.