Transact-SQL Variables

Accessing and Changing Relational Data

previous page next page
Accessing and Changing Relational Data

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

See Also

DECLARE @local_variable

SET @local_variable

SELECT

previous page start next page