Dynamic SQL Statement[Syntax]

Dynamic- SQL Statement

DECLARE @local_variable (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.

The Transact-SQL shown above declares two variables

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

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

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.