DECLARE Statement

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

Syntax

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

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

Parameters

initial-value – the variable is set to that value. The data type must match the type defined by data-type. If you do not specify an initial-value, the variable contains the NULL value until a SET statement assigns a different value.

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

Use the DECLARE statement to declare variables used in the body of a procedure. 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.

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 SAP 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 an SAP Sybase IQ SQL or Transact-SQL compound statement).

Permissions

None