DECLARE @local_variable (Transact-SQL)
Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement. Cursor variables can be declared with this statement and used with other cursor-related statements. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration.
DECLARE
{
{{ @local_variable [AS] data_type } | [ =value ] }
| { @cursor_variable_name CURSOR }
} [,...n]
| { @table_variable_name [AS] <table_type_definition> | <user-defined table type> }
<table_type_definition> ::=
TABLE ( { <column_definition> | <table_constraint> } [ ,... ]
) <column_definition> ::=
column_name { scalar_data_type | AS computed_column_expression }
[ COLLATE collation_name ]
[ [ DEFAULT constant_expression ] | IDENTITY [ ( seed ,increment ) ] ]
[ ROWGUIDCOL ]
[ <column_constraint> ]
<column_constraint> ::=
{ [ NULL | NOT NULL ]
| [ PRIMARY KEY | UNIQUE ]
| CHECK ( logical_expression )
}
<table_constraint> ::=
{ { PRIMARY KEY | UNIQUE } ( column_name [ ,... ] )
| CHECK ( search_condition )
}
Variables are often used in a batch or procedure as counters for WHILE, LOOP, or for an IF...ELSE block.
Variables can be used only in expressions, not in place of object names or keywords. To construct dynamic SQL statements, use EXECUTE.
The scope of a local variable is the batch in which it is declared.
A cursor variable that currently has a cursor assigned to it can be referenced as a source in a:
CLOSE statement.
DEALLOCATE statement.
FETCH statement.
OPEN statement.
Positioned DELETE or UPDATE statement.
SET CURSOR variable statement (on the right side).
In all of these statements, SQL Server raises an error if a referenced cursor variable exists but does not have a cursor currently allocated to it. If a referenced cursor variable does not exist, SQL Server raises the same error raised for an undeclared variable of another type.
A cursor variable:
Can be the target of either a cursor type or another cursor variable. For more information, see SET @local_variable (Transact-SQL).
Can be referenced as the target of an output cursor parameter in an EXECUTE statement if the cursor variable does not have a cursor currently assigned to it.
Should be regarded as a pointer to the cursor. For more information about cursor variables, see Transact-SQL Cursors.
A. Using DECLARE
The following example uses a local variable named @find to retrieve contact information for all last names beginning with Man.
USE AdventureWorks; GO DECLARE @find varchar(30); /* Also allowed: DECLARE @find varchar(30) = 'Man%'; */ SET @find = 'Man%'; SELECT LastName, FirstName, Phone FROM Person.Contact WHERE LastName LIKE @find;
Here is the result set.
LastName FirstName Phone -------------------------------------------------- -------------------------------------------------- ------------------------- Manchepalli Ajay 1 (11) 500 555-0174 Manek Parul 1 (11) 500 555-0146 Manzanares Tomas 1 (11) 500 555-0178 (3 row(s) affected)
B. Using DECLARE with two variables
The following example retrieves the names of Adventure Works Cycles sales representatives who are located in the North American sales territory and have at least $2,000,000 in sales for the year.
USE AdventureWorks; GO SET NOCOUNT ON; GO DECLARE @Group nvarchar(50), @Sales money; SET @Group = N'North America'; SET @Sales = 2000000; SET NOCOUNT OFF; SELECT FirstName, LastName, SalesYTD FROM Sales.vSalesPerson WHERE TerritoryGroup = @Group and SalesYTD >= @Sales;
C. Declaring a variable of type table
The following example creates a table variable that stores the values specified in the OUTPUT clause of the UPDATE statement. Two SELECT statements follow that return the values in @MyTableVar and the results of the update operation in the Employee table. Note that the results in the INSERTED.ModifiedDate column differ from the values in the ModifiedDate column in the Employee table. This is because the AFTER UPDATE trigger, which updates the value of ModifiedDate to the current date, is defined on the Employee table. However, the columns returned from OUTPUT reflect the data before triggers are fired. For more information, see OUTPUT Clause (Transact-SQL).
USE AdventureWorks;
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
ModifiedDate = GETDATE()
OUTPUT inserted.EmployeeID,
deleted.VacationHours,
inserted.VacationHours,
inserted.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
D. Declaring a variable of user-defined table type
The following example creates a table-valued parameter or table variable called @LocationTVP. This requires a corresponding user-defined table type called LocationTableType. For more information about how to create a user-defined table type, see CREATE TYPE (Transact-SQL). For more information about table-valued parameters, see Table-Valued Parameters (Database Engine).
DECLARE @LocationTVP AS LocationTableType;
