Variables in SQL procedures (DECLARE, SET statements)

Local variable support in SQL procedures allows you to assign and retrieve SQL values in support of SQL procedure logic.

Variables in SQL procedures are defined by using the DECLARE statement.

Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.

Variable values can be assigned to SQL procedure parameters, other variables in the SQL procedure, and can be referenced as parameters within SQL statements that executed within the routine.

  CREATE PROCEDURE proc_vars()
  SPECIFIC proc_vars
  LANGUAGE SQL
  BEGIN
     
    DECLARE v_rcount INTEGER;

    DECLARE v_max DECIMAL (9,2);

    DECLARE v_adate, v_another  DATE;    		 

    DECLARE v_total INTEGER DEFAULT 0;           -- (1)

    DECLARE v_rowsChanged BOOLEAN DEFAULT FALSE; -- (2)

    SET v_total = v_total + 1;                   -- (3)
	
    SELECT MAX(salary)                           -- (4)
      INTO v_max FROM employee;  	    		 

    VALUES CURRENT_DATE INTO v_date;             -- (5)

    SELECT CURRENT DATE, CURRENT DATE            -- (6)
         INTO v_adate, v_another
    FROM SYSIBM.SYSDUMMY1;

    DELETE FROM T; 
    GET DIAGNOSTICS v_rcount = ROW_COUNT;        -- (7)

    IF v_rcount > 0 THEN                         -- (8)
      SET is_done = TRUE;
    END IF;
  END

When declaring a variable, you can specify a default value using the DEFAULT clause as in line (1). Line (2) shows the declaration of a variable of the Boolean data type with a default value of FALSE. Line (3) shows that a SET statement can be used to assign a single variable value. Variables can also be set by executing a SELECT or FETCH statement in combination with the INTO clause as shown in line (4). Lines (5) and (6) show how the VALUES INTO statement can be used to evaluate a function or special register and assign the value to a variable or to multiple variables.

You can also assign the result of a GET DIAGNOSTICS statement to a variable. GET DIAGNOSTICS can be used to get a handle on the number of affected rows (updated for an UPDATE statement, DELETE for a DELETE statement) or to get the return status of a just executed SQL statement. Line (7) shows how the number of rows modified by the just previously executed DELETE statement can be assigned to a variable.

Line (8) demonstrates how a piece of logic can be used to determine the value to be assigned to a variable. In this case, if rows were changed as part of the earlier DELETE statement and the GET DIAGNOSTICS statement execution resulted in the variable v_rcount being assigned a value greater than zero, the variable is_done is assigned the value TRUE.