A variable is a user-friendly name given to an area of memory that stores a value. Variables are one of the most common elements of Transact-SQL programming.
When to use Variables
Variables are useful in many situations. You will find yourself using them when:
Declaring parameters for stored procedures
Storing values used in calculations
Looping a specific number of times
Declaring a variable
Variables always start with the @ sign, as shown below:
The following code declares a variable named LoopCount, defining it as an integer data type:
DECLARE @LoopCount int
You do not need to “undeclare” a variable, because the memory will be freed when the batch of Transact-SQL statements is finished.
Assigning a value
You assign a value to a variable with either the SELECT or the SET keyword.
The following code increments the value of @LoopCount by 1:
SELECT @LoopCount = @LoopCount + 1
This code assigns the value of zero to the @LoopCount variable:
SET @LoopCount = 0
Although you can use SET and SELECT interchangeably, SET is preferred by Microsoft.
Availability of a variable
The availability of your variable varies depending on whether you are using Transact-SQL or the Query Analyzer to process queries:
If you use the Query Analyzer to execute Transact-SQL statements, the variable will be available throughout all the Transact-SQL statements within the Query Analyzer window, unless you have separated the code into batches with the GO keyword.
If you use Transact-SQL, your variable will be available for the duration of the batch in which it is created. Therefore, if you declare a variable within a stored procedure, it is available everywhere within the stored procedure, but not to others.
Using a variable in Transact-SQL
The following Slide Show contains examples of declaring, assigning a value, and using a variable in Transact-SQL:
The variable @LoopCount is declared as an integer.
@LoopCount is assigned a value of 17
All values in which EmployeeId equals 17 are retrieved from the Employer table.
In the next lesson, functions within your Transact-SQL programming will be discussed.