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

DECLARE exception-name EXCEPTION
FOR SQLSTATE [ VALUE ] string

DECLARE variable-name: Variables used in the body of a procedure, trigger, or batch can be declared using the DECLARE statement using Syntax 1. The variable persists for the duration of the compound statement in which it is declared. 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 different value is assigned by the SET statement.

The body of a Watcom SQL procedure or trigger is a compound statement, and variables must be declared with other declarations, such as a cursor declaration (DECLARE CURSOR), immediately following the BEGIN keyword. In a Transact-SQL procedure or trigger, there is no such restriction.

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

DECLARE exception-name EXCEPTION: Use this syntax to declare variables for SQL language exceptions within a compound statement (BEGIN...END). The variables can be used, for example, for comparison with the SQLSTATEs obtained during execution, with the SIGNAL statement, or as part of the exception case within the exception handler.

None.

None.

• SQL data types
• DECLARE statement
• DECLARE CURSOR statement [ESQL] [SP]
• BEGIN statement
• SQLSTATE special value
• Special values
• Exception handlers

The following batch illustrates the use of the DECLARE statement and prints a message in the database server messages window:

BEGIN
  DECLARE varname CHAR(61);
  SET varname = 'Test name';
  MESSAGE varname;
END

This example declares the following variables:

• v1 as an INT with the initial setting of 5.
• v2 and v3 as CHAR(10), both with an initial value of abc.

BEGIN
   DECLARE v1 INT = 5;
   DECLARE v2, v3 CHAR(10) = 'abc';
			// ...
END

The following procedure declares an exception for use with the SQLSTATE comparison:

CREATE PROCEDURE HighSales (IN cutoff INT, OUT HighValues INT)
BEGIN
   DECLARE err_notfound EXCEPTION FOR
      SQLSTATE '02000';
   DECLARE curThisCust CURSOR FOR
      SELECT CAST( sum( SalesOrderItems.Quantity *
            Products.UnitPrice ) AS INTEGER) VALUE
      FROM Customers
            LEFT OUTER JOIN SalesOrders
            LEFT OUTER JOIN SalesOrderItems
            LEFT OUTER JOIN Products
      GROUP BY CompanyName;
   DECLARE ThisValue INT;
   SET HighValues = 0;
   OPEN curThisCust;
   CustomerLoop:
   LOOP
      FETCH NEXT curThisCust
         INTO ThisValue;
      IF SQLSTATE = err_notfound THEN
         LEAVE CustomerLoop;
      END IF;
      IF ThisValue > cutoff THEN
         SET HighValues = HighValues + ThisValue;
      END IF;
END LOOP CustomerLoop;
   CLOSE curThisCust;
END;

The following compound statement declares an exception for use with SIGNAL and an exception handler:

BEGIN
   DECLARE err_div_by_0 EXCEPTION FOR
      SQLSTATE '22012';
   DECLARE curQuantity CURSOR FOR
      SELECT Quantity
      FROM SalesOrderItems
      WHERE ProductID = 300;
   DECLARE Quantities INT;
   DECLARE altogether INT;
   SET Quantities = 0;
   SET altogether = 0;
   OPEN curQuantity;
   LOOP
      FETCH NEXT curQuantity
         INTO Quantities;
      IF SQLSTATE = '02000' THEN
         SIGNAL err_div_by_0;
      END IF;
      SET altogether = altogether + Quantities;
   END LOOP;
EXCEPTION
   WHEN err_div_by_0 THEN
	CLOSE curQuantity;
	SELECT altogether;
    return;
   WHEN OTHERS THEN
	RESIGNAL;
END;