Transact-SQL Variables

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 time 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 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

See Also