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

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:


  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.