DECLARE {LOCAL_}VARIABLE (Transact-SQL)
Declares a local variable. Local variables are used to store values. Local variables can be used within a batch, stored procedure, or script.
Syntax
DECLARE
{ [ @ ] variable_name [ ,...n ] data_type
[ = <expression> ]
}
[;] Arguments
@
Optional. Indicates that the variable name is a local variable.
variable_name
Is the name of the local variable. Variable names must start with the at sign (@).
data_type
Is any system-supplied data type, user-defined data type, or alias data type. If the data type is not specified, it defaults to int.
= <expression>
Is the initial value assigned to the variable. The expression can be a constant value, a function, or another variable.
Permissions
Requires membership in the public role.
Examples
A. Declaring and initializing a local variable
The following example declares a local variable named @mycount and initializes it to 10.
DECLARE @mycount INT = 10;
SELECT @mycount AS MyCount; B. Declaring multiple local variables
The following example declares and initializes two local variables.
DECLARE @dept_name VARCHAR(50) = 'Sales',
@emp_count INT = 50;
SELECT @dept_name AS Department, @emp_count AS EmployeeCount; C. Declaring a variable without an initial value
The following example declares a local variable named @mydate. It defaults to NULL.
DECLARE @mydate DATETIME;
SELECT @mydate AS MyDate; D. Declaring a variable based on a column's data type
The following example declares a variable that has the same data type as the name column in the Person.Person table.
DECLARE @personName NVarChar(50);
SELECT @personName = name
FROM Person.Person
WHERE BusinessEntityID = 1;
SELECT @personName AS PersonName;