Using SELECT INTO with Dynamic SQL in GBase 8a: Why Local Variables Fail

When executing dynamic SQL inside a GBase 8a stored procedure, you cannot INTO a locally declared variable (DECLARE). You must use a session‑level variable (prefixed with @) instead. This article demonstrates the difference with two clear examples from a gbase database.

Local Variable: Error 1327

If you try to fetch the result of a dynamic statement directly into a local variable, the procedure fails with ERROR 1327 (42000): Undeclared variable: num.

DROP PROCEDURE IF EXISTS get_gc_task2;
DELIMITER //
CREATE PROCEDURE get_gc_task2(IN strlen INT, IN top INT)
BEGIN
  DECLARE num INT;
  SET @executeSQL_sql = 'SELECT count(*) INTO num FROM t1';
  SELECT @executeSQL_sql;
  PREPARE executeSQL_s1 FROM @executeSQL_sql;
  EXECUTE executeSQL_s1;
  DEALLOCATE PREPARE executeSQL_s1;

  SELECT strlen, top, NOW(), num FROM dual;
END //
DELIMITER ;
CALL get_gc_task2(1,2);

Session Variable: Success

Rewrite the dynamic SQL to store the result in a session variable (e.g., @get_gc_task2_AAA), then assign it to the local variable after execution. This works perfectly.

DROP PROCEDURE IF EXISTS get_gc_task2;
DELIMITER //
CREATE PROCEDURE get_gc_task2(IN strlen INT, IN top INT)
BEGIN
  DECLARE num INT;
  SET @executeSQL_sql = 'SELECT count(*) INTO @get_gc_task2_AAA FROM t1';
  SELECT @executeSQL_sql;
  PREPARE executeSQL_s1 FROM @executeSQL_sql;
  EXECUTE executeSQL_s1;
  DEALLOCATE PREPARE executeSQL_s1;
  SET num = @get_gc_task2_AAA;
  SELECT strlen, top, NOW(), num FROM dual;
END //
DELIMITER ;
CALL get_gc_task2(1,2);

Why This Happens

Dynamic SQL runs in a separate context that cannot access the caller's local variables. However, session‑level variables (@...) are shared across the entire session, so they can bridge the gap. After the dynamic statement finishes, simply copy the session variable's value into your local variable.

Naming Caution

Since session variables are session‑scoped, avoid name collisions by prefixing them with the procedure name (e.g., @get_gc_task2_AAA). This prevents accidental interference between different routines.

This small but important distinction saves you from one of the most confusing errors when writing dynamic SQL in GBASE's GBase 8a. Keep it in mind whenever you combine EXECUTE with INTO.