ASA SQL Reference
  SQL Language Elements
    Variables

Local variables

Local variables are declared using the DECLARE statement, which can be used only within a compound statement (that is, bracketed by the BEGIN and END keywords). The variable is initially set as NULL. The value of the variable can be set using the SET statement, or can be assigned using a SELECT statement with an INTO clause.

The syntax of the DECLARE statement is as follows:

DECLARE variable-name data-type

Local variables can be passed as arguments to procedures, as long as the procedure is called from within the compound statement.

Examples 

• The following batch illustrates the use of local variables.

  BEGIN
     DECLARE local_var INT;
     SET local_var = 10;
     MESSAGE 'local_var = ', local_var TO CLIENT;
  END

  Running this batch from Interactive SQL gives the message local_var = 10 in the Interactive SQL Messages pane.

• The variable local_var does not exist outside the compound statement in which it is declared. The following batch is invalid, and gives a column not found error.

  -- This batch is invalid.
  BEGIN
     DECLARE local_var INT;
     SET local_var = 10;
  END;
  MESSAGE 'local_var = ', local_var TO CLIENT;

• The following example illustrates the use of SELECT with an INTO clause to set the value of a local variable:

  BEGIN
     DECLARE local_var INT;
     SELECT 10 INTO local_var;
     MESSAGE 'local_var = ', local_var TO CLIENT;
  END

  Running this batch from Interactive SQL gives the message local_var = 10 on the server window.

Standards and compatibility 

• Names    Adaptive Server Enterprise and Adaptive Server Anywhere both support local variables. In Adaptive Server Enterprise, all variables must be prefixed with an @ sign. In Adaptive Server Anywhere, the @ prefix is optional. To write compatible SQL, prefix all of your variables with @.

• Scope    The scope of local variables is different in Adaptive Server Anywhere and Adaptive Server Enterprise. Adaptive Server Anywhere supports the use of the DECLARE statement to declare local variables within a batch. However, if the DECLARE is executed within a compound statement, the scope is limited to the compound statement.

• Declaration    Only one variable can be declared for each DECLARE statement in Adaptive Server Anywhere. In Adaptive Server Enterprise, more than one variable can be declared in a single statement.

For more information on batches and local variable scope, see Variables in Transact-SQL procedures.