Declaration of Transact-SQL Variables

The following describes the declaration of the Transact-SQL variable for your reference. You can use the DECLARE statement to initialize the Transact-SQL variable through the following operations:

Assignment name. The first character of the name must be @.
Assign the data type and length provided by the system or defined by the user. Precision and decimal places are also specified for numeric variables.
Set the value to NULL.

This section describes how to use the data types provided by the system for local variables to minimize future maintenance issues.

For example, the following DECLARE statement uses the int data type to create a local variable named @ mycounter.

DECLARE @ MyCounter INT

To declare multiple local variables, use a comma after the first defined local variable, and specify the name and Data Type of the next local variable.

For example, the following DECLARE statement creates three local variables named @ last_name, @ fname, and @ state, and initializes each variable 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 the SQL variable starts from where the variable is declared to the end of the batch processing or stored procedure of the declared variable. For example, the following script generates a syntax error because the SQL variables referenced in one batch are defined in another batch:

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

Set the value in the Transact-SQL variable

SQL variable definition assignment and calling

Teaches you to use SQL parameter variables to pass record values

Application Example of system variables in SQL

SQL Server Distributed Query