Server Interaction  «Prev  Next»

Lesson 2SQL - Server Variables
ObjectiveDeclare, assign, and use variables in SQL-Server

Declare, assign, and use variables in SQL-Server

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:
  1. Declaring parameters for stored procedures
  2. Using cursors
  3. Storing values used in calculations
  4. Looping a specific number of times

Declaring a variable

Variables always start with the @ sign, as shown below:

Declare SQL Server variable
Declare SQL Server variable

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:
  1. 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.
  2. 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.
1) The variable @LoopCount is declared as an integer.
@LoopCount is assigned a value of 17
2) @LoopCount is assigned a value of 17
All values in which EmployeeId equals 17 are retrieved from the Employer table.
3) All values in which EmployeeId equals 17 are retrieved from the Employer table.

  1. The variable @LoopCount is declared as an integer.
  2. @LoopCount is assigned a value of 17
  3. All values in which EmployeeId equals 17 are retrieved from the Employer table.

Microsoft SQL-Server
In the next lesson, functions within your Transact-SQL programming will be discussed.