Variables are coding objects used in all programming languages. A variable is a temporary storage location in memory, given a name and assigned a value. The length of time a variable exists in memory varies by language and usage. In T-SQL, a variable exists only for the time it takes to run the script where it is defined. The variable does not exist before the script is run, or afterward.
Most languages use some type of variable declaration statement, a way to name and reserve memory space for a variable, by data type. In T-SQL variables are defined using the DECLARE keyword, followed by one or more variable definitions. Multiple definitions are separated by commas. Each variable definition contains an "@" prefix, a name, and a datatype. For example:
Once a variable is declared, it can also be assigned or given a value. This sets the value of the variable so it can be used later in code. Variables in T-SQL can be assigned an initial value in the DECLARE statement. For example:
It is also be possible to define the variables in a single line:
To assign a value to a variable after it is declared, the SET statement is used. For example:
In testing, both the SELECT and PRINT statements can be used to display the value of a variable in Query Manager.
In this usage, SELECT statement does not need a table name, and will pull the values from memory.
Using Variables in Other Statements
A variable can take the place of any literal in a T-SQL statement. In the following examples, the variables take the place of literals in the WHERE clause:
Note that all the above statements must be executed at the same time. It is not possible to execute the DECLARE statements to declare the variables and then run the SELECT statements separately. Variables only exist when the script they are in is running. Once it finishes, the variables are destroyed and no longer exist.
Assigning Variable Values In a Query
Variables can also be assigned values directly from a SELECT statement:
The above example demonstrates setting the value based on the results of a query. Note, however, that in each case, the query returns a single result. The second example uses an aggregate to return a single value for the largest credit limit. The query would result in an error if there was an attempt to asssign a value to a variable when the query returns multiple records.
If a query statement of this type fails (return zero records), then any variables would not be set and retain the current values. Take the following examples:
More than one variable can be assigned a value directly from a SELECT statement:
Variables can be used in other ways, such as in calculations, or as parameter values in functions. Here is a longer example using the techniques discussed above:
Again, note that the variables exist ONLY when the entire script is run. They are declared and exist starting with the first statement, and cease to exist when the script completes execution. The statements CANNOT be run one at a time, because the variables would not exist in any of the lines after the DECLARE statement.
Here is an example of a variable being used in a function call, with a query:
And another example with a function: