Lesson 6	Constructing SQL statements dynamically
Objective	Describe how to construct SQL statements dynamically.

Constructing SQL Statements dynamically in SQL-Server

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.

SQL Server Concatenated Strings

SQL Server (starting with 2008)
Azure SQL Database
Azure SQL Data Warehouse
Parallel Data Warehouse

An operator in a string expression that concatenates two or more character or binary strings, columns, or a combination of strings and column names into one expression (a string operator).

expression + expression

-- Azure SQL Data Warehouse and Parallel Data Warehouse
expression 
+
expression

expression:Is any valid expression of any one of the data types in the character and binary data type category, except the image, ntext, or text data types. Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression. An explicit conversion to character data must be used when concatenating binary strings and any characters between the binary strings. The following example shows when CONVERT, or CAST, must be used with binary concatenation and when CONVERT, or CAST, does not have to be used.

It is important to know how to concatenate strings together in SQL Server. Every piece of the string that is concatenated must be a character data type and String literals must be enclosed in quotes. Unlike other programming languages, such as Visual Basic (which uses the ampersand &, SQL Server uses the plus sign (“+”) to concatenate strings together. If you are using string literals to concatenate your strings, make sure that your quotes are in the correct place. Otherwise, SQL Server might treat your variable names as string literals.
Using Dynamic SQL allows you to use logic in your queries, too. Because building Transact-SQL statements dynamically is nothing more than building a string, you can concatenate strings around any valid logic constructs, like

IF…THEN
ELSE

, loops, and other conditional constructs.
Each variable that holds your Transact-SQL statements cannot contain more than 4000 characters. Therefore, if your statement is longer, you must issue multiple variables to the EXEC function.

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.

1) The Transact-SQL shown above declares two variables

2) one called @TableName that holds the name of the table to retrieve data from.

3) The other, called @SQL, holds the Transact-SQL statement that is built dynamically.

4) After @TableName is assigned the value of SalaryHistory, the Transact-SQL statement is built dynamically.

The value stored in @TableName is appended to SELECT * FROM, resulting in SELECT * FROM SalaryHistory. This is what is executed by SQL Server. — 5_ The value stored in @TableName is appended to SELECT * FROM, resulting in

SELECT * FROM SalaryHistory.
This is what is executed by SQL Server.

How to DECLARE @local_variable in Transact-SQL

Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement. Cursor variables can be declared with this statement and used with other cursor-related statements. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration.

DECLARE 
{ 
    { @local_variable [AS] data_type  | [ = value ] }
  | { @cursor_variable_name CURSOR }
} [,...n] 
| { @table_variable_name [AS] <table_type_definition> } 

<table_type_definition> ::= 
     TABLE ( { <column_definition> | <table_constraint> } [ ,... ] ) 

<column_definition> ::= 
     column_name { scalar_data_type | AS computed_column_expression }
     [ COLLATE collation_name ] 
     [ [ DEFAULT constant_expression ] | IDENTITY [ (seed ,increment ) ] ] 
     [ ROWGUIDCOL ] 
     [ <column_constraint> ] 

<column_constraint> ::= 
     { [ NULL | NOT NULL ] 
     | [ PRIMARY KEY | UNIQUE ] 
     | CHECK ( logical_expression ) 
     | WITH ( <index_option > )
     } 

<table_constraint> ::= 
     { { PRIMARY KEY | UNIQUE } ( column_name [ ,... ] ) 
     | CHECK ( search_condition ) 
     } 

<index_option> ::=
See CREATE TABLE for index option syntax.

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