DECLARE @local_variable (T-SQL)

Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values with 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.

Syntax

DECLARE
    {
        {@local_variable data_type}
        | {@cursor_variable_name CURSOR}
    } [,...n]

Arguments

@local_variable: Is the name of a variable. Variable names must begin with an at sign (@). Local variable names must conform to the rules for identifiers. For more information, see Using Identifiers.
data_type: Is any system-supplied or user-defined data type. A variable cannot be of text, ntext, or image data type. For more information about system data types, see Data Types. For more information about user-defined data types, see sp_addtype.
@cursor_variable_name: Is the name of a cursor variable. Cursor variable names must begin with an at sign (@) and conform to the rules for identifiers. For more information, see Using Identifiers.
CURSOR: Specifies that the variable is a local, cursor variable.
n: Is a placeholder indicating that multiple variables can be specified and assigned values.

Remarks

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, stored procedure, or statement block in which it is declared. For more information about using local variables in statement blocks, see Using BEGIN...END.

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 these statements, Microsoft® 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.
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.

Examples

A. Use DECLARE

This example uses a local variable named @find to retrieve author information for all authors with last names beginning with Ring.

USE pubs

DECLARE @find varchar(30)

SET @find = 'Ring%'

SELECT au_lname, au_fname, phone

FROM authors

WHERE au_lname LIKE @find

Here is the result set:

au_lname au_fname phone

-------------------------------------- -------------------- ------------

Ringer Anne 801 826-0752

Ringer Albert 801 826-0752

(2 row(s) affected)

B. Use DECLARE with two variables

This example retrieves employee names from employees of Binnet & Hardley (pub_id = 0877) who were hired on or after January 1, 1993.

USE pubs

SET NOCOUNT ON

GO

DECLARE @pub_id char(4), @hire_date datetime

SET @pub_id = '0877'

SET @hire_date = '1/01/93'

-- Here is the SELECT statement syntax to assign values to two local

-- variables.

-- SELECT @pub_id = '0877', @hire_date = '1/01/93'

SET NOCOUNT OFF

SELECT fname, lname

FROM employee

WHERE pub_id = @pub_id and hire_date >= @hire_date

Here is the result set:

fname lname

-------------------- ------------------------------

Anabela Domingues

Paul Henriot