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. You can set the value of the variable using the SET statement, or you can assign the value using a SELECT statement with an INTO clause.

The syntax of the DECLARE statement is as follows:

DECLARE variable-name data-type

You can pass local variables as arguments to procedures, as long as the procedure is called from within the compound statement.

• The following batch illustrates the use of local variables:

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

  Running this batch from ISQL displays this message on the server window:

  local_var = 10
  

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

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

• 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 ;
  END
  

  Running this batch from ISQL displays this message on the server window:

  local_var = 10