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.
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.
Before you can do anything with a local variable, you need to declare it. Declaration 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
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
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'
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.