DECLARE statement

Declares a SQL variable (connection-scope) or an exception within a compound statement (BEGIN...END).

Syntax

Declaring a variable

DECLARE identifier [, ... ] data-type
[ { = | DEFAULT } initial-value ]

initial-value : expression

Declaring an exception

DECLARE exception-name EXCEPTION
FOR SQLSTATE [ VALUE ] string

Parameters

identifier
A valid identifier for the variable.
data-type
The data type for the variable. Set the data type explicitly, or you can set it by using the %TYPE or %ROWTYPE attribute. Use %TYPE to set it to the data type of a variable or a column in a table or view. Use %ROWTYPE to set the data type to a composite data type derived from a row in a cursor, table, or view.
DEFAULT clause
The default value for the variable. If you specify initial-value, the data type must match the type defined by data-type.
initial-value
The default and initial value for the variable. initial-value must match the data type defined by data-type. If you do not specify an initial-value, the variable contains the NULL value until a different value is assigned.

Remarks

DECLARE variable-name: Variables used in the body of a procedure, trigger, or batch can be declared using the DECLARE statement. The variable persists for the duration of the compound statement in which it is declared.

If you specify a variable name for initial-value, the variable must already be initialized in the database.

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.

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.

Privileges

None.

Side effects

None.

Standards

ANSI/ISO SQL Standard
Declaring variables syntax - Persistent Stored Module feature. Declaring exceptions syntax - The form of exception declaration supported by the software, namely the DECLARE EXCEPTION statement, is not in the standard; in the ANSI/ISO SQL Standard, exceptions are specified using a handler declaration using the keywords DECLARE HANDLER. The DECLARE...EXCEPTION syntax is not allowed in T-SQL procedures.
Transact-SQL
The syntax for declaring exceptions cannot be used in Transact-SQL compound statements and procedures.

Example

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;

The following statement creates a procedure, DepartmentsCloseToCustomerLocation, and sets its IN parameter to the data type of the ID column in the Customers table by using a %TYPE attribute:

CREATE OR REPLACE PROCEDURE DepartmentsCloseToCustomerLocation( IN customer_ID Customers.ID%TYPE )
  BEGIN
    DECLARE cust_rec Customers%ROWTYPE;

    SELECT City, State, Country
    INTO cust_rec.City, cust_rec.State, cust_rec.Country
    FROM Customers
    WHERE ID = customer_ID;

    SELECT Employees.Surname, Employees.GivenName, Departments.DepartmentName
    FROM Employees JOIN Departments
    ON Departments.DepartmentHeadID = Employees.EmployeeID
    WHERE Employees.City = cust_rec.City
    AND Employees.State = cust_rec.State
    AND Employees.Country = cust_rec.Country;
  END;

CALL DepartmentsCloseToCustomerLocation(158);

The following example uses the %ROWTYPE attribute to create a variable, @a_product, and then inserts data from the Products table into the variable:

CREATE OR REPLACE PROCEDURE CheckStock (
    IN @id Products.ID%TYPE
)
   BEGIN
    DECLARE @a_product Products%ROWTYPE;
    SET (@a_product).ID = 200;
   END;