Variables
Variables in Transact-SQL are the equivalent of variables in other programming languages, but due to the nature of the Transact-SQL language, their use and behavior are somewhat different.
SQL Server 2005 (and 2000) documentation recognizes only local variables and table variables. Documentation in SQL Server 7 and earlier versions was also referring to global variables. In SQL Server 2005 (and 2000) global variables are considered to be functions.

Local Variables
The scope of local variables is a batch (a set of T-SQL statements that is sent to SQL Server and executed simultaneously). This restriction implicitly includes a single stored procedure (because stored procedures are defined in a batch). This is a significant limitation. However, several workarounds can be used as solutions to this problem.
A stored procedure cannot access variables defined in other stored procedures. One way to pass values to and from stored procedures is to use parameters. Keep in mind that you are passing only the values associated with the variables, not references, as you can in some other programming languages.
Another way to transfer value between stored procedures or between batches is the use of more permanent database objects such as tables or temporary tables.
Let's review basic operations with local variables.

Declaring Variables
Before you can do anything with a local variable, you need to declare it. Declaration

    Requires Free Membership to View

consists of the reserved word Declare and a list of variables and their respective data types.
The names of variables must comply with the rules for identifiers with one exception—they must begin with @:

Declare @LastName varchar(50)

It is possible to define several variables in a single Declare statement. You just need to separate them with commas:

Declare   @LastName varchar(50),
          @FirstName varchar(30),
          @BirthDate smalldatetime

NOTE
One stored procedure or a batch can have up to 10,000 local variables.

You can define variables based on user-defined data types:

Declare @OfficePhone phone

NOTE
You cannot define the nullability of the variable, as you can with table columns. This does not mean that variables cannot contain null values. In fact, before assignment, the value of each variable is null. It is also possible to explicitly set the value of each variable to null.

Assigning Values with the Select Statement
There are several ways to assign a value to a local variable. In early versions of SQL Server, the only way to do this was to use a modification of the Select statement:

Select @LastName = 'Smith'

It is also possible to assign several variables in the same statement:

Select    @LastName = 'Smith',
          @FirstName = 'David',
          @BirthDate = '2/21/1965'

NOTE
It is necessary to assign a value of an appropriate data type to the variable; however, there are some workarounds. In some cases, the server will perform an implicit conversion from one data type to another. SQL Server also includes a set of functions for explicit conversion. Convert() and Cast() can be used to change the data type of the value (see Chapter 4). Some data types are not compatible, so explicit conversion is the only solution.

Quite often, variables are assigned values from the result set of the Select statement:

Select    @Make = Equipment.Make,
          @Model = Equipment.Model,
          @EqType = Equipment.EqType
From EqType INNER JOIN Equipment
     ON EqType.EqTypeId = Equipment.EqTypeId
Where EqId = 2

There are some potential problems associated with this approach. How will the server assign values if the result set contains multiple records, or no records?
If more than one record is returned in the result set, a variable will be assigned the values from the last record. The only trouble is that we cannot predict which record will be the last, because this position depends on the index that the server uses to create the result set.
It is possible to create workarounds to exploit these facts (that is, to use hints to specify an index or use minimum and/or maximum functions to assign extreme values). The recommended solution, however, is to narrow the search criteria so that only one record is returned.
The other behavior that might cause unexpected results is the case in which a result set does not return any records. It is a common belief and expectation of many developers that the variable will be set to null. This is absolutely incorrect. The content of the variable will not be changed in this case.
Observe the following example, or try to run it against the Asset5 database:

Declare   @make varchar(50),
          @model varchar(50),
          @EqType varchar(50)

Select    @Make = 'ACME',
          @Model = 'Turbo',
          @EqType = 'cabadaster'

Select    @Make = make,
          @Model = Model,
          @EqType = EqType.EqType
From EqType INNER JOIN Equipment
     ON EqType.EqTypeId = Equipment.EqTypeId
Where EqId = -1

Select @make make, @model model, @EqType EqType

Since the Equipment table does not have a record with the identifier set to –1, the variables will keep their original values.

make                    model                      EqType
----------------------- -------------------------- --------------
ACME                    Turbo                      cabadaster
(1 row(s) affected)

Only if the values of the variables were not previously set will they continue to contain a null value.
The variable can be assigned with any Transact-SQL expression such as a constant or a calculation, or even a complete Select statement that returns a single value:

Select    @Make = Make,
          @Model = Model,
          @EquipmentName = Make + ' ' + Model,
          @EqType = (select EqType
                    from dbo.EqType EqType
                    where EqType.EqTypeId = Equipment.EqTypeId)
From dbo.Equipment
Where EqId = 2 

There is one combination of statements and expressions that will result in a syntax error. It is not possible to return a result set from the Select statement and to assign a variable in the same Select statement:

Select    Make,
          @Model = Model    -- wrong
From dbo.Equipment
Where EqId = 2

Assigning Values with the Set Statement
In SQL Server 7.0, the syntax of the Set statement has been expanded to support the assignment of local variables. In earlier versions, it was possible to use the Set statement only to declare cursor variables. Today, Microsoft is proclaiming this as a preferred method for assigning variables:

Set @LastName = 'Johnson'

Use of the Set statement is preferable, since it makes code more readable and reduces the opportunity to make a mistake (assign a variable and return a result set at the same time).
There is just one problem with the Set statement—it is not possible to assign several values with one statement. You will be forced to write code like this:

Set     @Make = 'ACME'
Set     @Model = 'Turbo'
Set     @EqType = 'cabadaster'

Assigning Values in the Update Statement
The ability to set the values of local variables in an Update statement is a feature that is buried deep in the oceans of SQL Server Books OnLine. It is an element that was designed to solve concurrency issues when code needs to read and update a column concurrently:

Update Inventory
Set @mnsCost = Cost = Cost * @fltTaxRate
Where InventoryId = @intInventoryId

Displaying the Values of Variables
The value of a variable can be displayed to the user by using a Select or a Print statement:

Select @LastName
Print @FirstName

It is possible to include a local variable in a result set that will be returned to the user:

Select    make "Selected make",
          Model "Selected Model",
          @Model "Original model"
From Equipment
Where EqId = 2


Basic Transact-SQL Programming Constructs

 Home: Introduction
 Tip 1: T-SQL identifiers
 Tip 2: Database object qualifiers
 Tip 3: Character string data types
 Tip 4: Date, time and number data types
 Tip 5: Special data types-Part 1
 Tip 6: Special data types-Part 2
 Tip 7: Local variables
 Tip 8: Global variables
 Tip 9: Table variables
 Tip 10: Flow control statements
 Tip 11: Blocks, looping, and conditional statements
 Tip 12: Unconditional and scheduled execution
 Tip 13: Transact SQL cursors
 Tip 14: Cursor related statements
 Tip 15: Problems and justified use of cursors

The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.


This was first published in February 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: