A Transact-SQL local variable is an object that can hold a single data value of a specific type. Variables in batches and scripts are typically used:
- As a counter either to count the number of times a loop is performed or to control how many times the loop is performed.
- To hold a data value to be tested by a control-of-flow statement.
- To save a data value to be returned by a stored procedure return code.
The following script creates a small test table and populates it with 26 rows. The script uses a variable to do three things:
- Control how many rows are inserted by controlling how many times the loop is executed.
- Supply the value inserted into the integer column.
- Function as part of the expression that generates letters to be inserted into the character column.
-- Create the table. CREATE TABLE TestTable (cola INT, colb CHAR(3)) GO SET NOCOUNT ON GO -- Declare the variable to be used. DECLARE @MyCounter INT -- Initialize the variable. SET @MyCounter = 0 -- Test the variable to see if the loop is finished. WHILE (@MyCounter < 26) BEGIN -- Insert a row into the table. INSERT INTO TestTable VALUES -- Use the variable to provide the integer value -- for cola. Also use it to generate a unique letter -- for each row. Use the ASCII function to get the -- integer value of 'a'. Add @MyCounter. Use CHAR to -- convert the sum back to the character @MyCounter -- characters after 'a'. (@MyCounter, CHAR( ( @MyCounter + ASCII('a') ) ) ) -- Increment the variable to count this iteration -- of the loop. SET @MyCounter = @MyCounter + 1 END GO SET NOCOUNT OFF GO
Declaring a Transact-SQL Variable
The DECLARE statement initializes a Transact-SQL variable by:
- Assigning a name. The name must have a single @ as the first character.
- Assigning a system-supplied or user-defined data type and a length. For numeric variables, a precision and scale are also assigned.
- Setting the value to NULL.
Note Use system-supplied data types for local variables to minimize future maintenance issues.
For example, the following DECLARE statement creates a local variable named @mycounter with an int data type.
DECLARE @MyCounter INT
To declare more than one local variable, use a comma after the first local variable defined, and then specify the next local variable name and data type.
For example, this DECLARE statement creates three local variables named @last_name, @fname and @state, and initializes each to NULL:
DECLARE @LastName NVARCHAR(30), @FirstName NVARCHAR(20), @State NCHAR(2)
The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared. For example, this script generates a syntax error because the variable is declared in one batch and referenced in another:
DECLARE MyVariable INT SET @MyVariable = 1 GO -- This terminates the batch. -- @MyVariable has gone out of scope and no longer exists. -- This SELECT statement gets a syntax error because it is -- no longer legal to reference @MyVariable. SELECT * FROM Employees WHERE EmployeeID = @MyVariable
Setting a Value in a Transact-SQL Variable
When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
To assign a variable a value by using the SET statement, include the variable name and the value to assign to the variable. This is the preferred method of assigning a value to a variable. This batch, for example, declares two variables, assigns values to them, and then uses them in the WHERE clause of a SELECT statement:
USE Northwind GO -- Declare two variables. DECLARE @FirstNameVariable NVARCHAR(20), @RegionVariable NVARCHAR(30) -- Set their values. SET @FirstNameVariable = N'Anne' SET @RegionVariable = N'WA' -- Use them in the WHERE clause of a SELECT statement. SELECT LastName, FirstName, Title FROM Employees WHERE FirstName = @FirstNameVariable OR Region = @RegionVariable GO
A variable can also have a value assigned by being referenced in a select list. If a variable is referenced in a select list, it should be assigned a scalar value or the SELECT statement should only return one row. For example:
USE Northwind GO DECLARE @EmpIDVariable INT SELECT @EmpIDVariable = MAX(EmployeeID) FROM Employees GO
If a SELECT statement returns more than one row and the variable references a nonscalar expression, the variable is set to the value returned for the expression in the last row of the result set. For example, in this batch @EmpIDVariable is set to the EmployeeID value of the last row returned, which is 1:
USE Northwind GO DECLARE @EmpIDVariable INT SELECT @EmpIDVariable = EmployeeID FROM Employees ORDER BY EmployeeID DESC SELECT @EmpIDVariable GO