DECLARE Statement

Declares a SQL variable within a compound statement (BEGIN... END).

Syntax

DECLARE 
variable_name  [ , … ] 
data-type  [{
= 
| DEFAULT} 
initial-value]

Parameters

initial-value: – special-value | string | [ - ] number | ( constant-expression ) | built-in-function ( constant-expression ) | NULL
special-value: – CURRENT { DATABASE | DATE | PUBLISHER | TIME | TIMESTAMP | USER | UTC TIMESTAMP } | USER

Examples

Example 1 – This batch illustrates the use of the DECLARE statement and prints a message on the server window:
```
BEGIN
  DECLARE varname CHAR(61);
  SET varname = 'Test name';
  MESSAGE varname;
END
```

Usage

Variables used in the body of a procedure can be declared using the DECLARE statement. The variable persists for the duration of the compound statement in which it is declared and must be unique within the compound statement.

The body of a procedure is a compound statement, and variables must be declared immediately following BEGIN. In a Transact-SQL procedure or trigger, there is no such restriction.

If you specify initial-value, the variable is set to that value. If you do not specify an initial-value, the variable contains the NULL value until a SET statement assigns a different value.

If you specify initial-value, the data type must match the type defined by data-type.

Standards

SQL—ISO/ANSI SQL compliant.
Sybase—Supported by Adaptive Server Enterprise.
- To be compatible with Adaptive Server Enterprise, the variable name must be preceded by an @.
- In Adaptive Server Enterprise, a variable that is declared in a procedure or trigger exists for the duration of the procedure or trigger. In Sybase IQ, if a variable is declared inside a compound statement, it exists only for the duration of that compound statement (whether it is declared in a Sybase IQ SQL or Transact-SQL compound statement).

Permissions

None