How can I assign a value to a variable declared outside the exception in bigquery?

Question

I am using a stored procedure and I need to assign the value to a variable declared outside the exception, the idea is to be able to call another stored procedure with additional variables when an error occurs

BEGIN
DECLARE v_error string;
SELECT 1/0; --division by zero: 1 / 0
EXCEPTION WHEN ERROR THEN
SET v_error = CONCAT('execution error: ',@@error.message, ' Query: ',@@error.statement_text);
END;

the result is:

Undeclared variable: v_error at [5:5]

Answer 1

If you want to bring any value from the main block into the EXCEPTION block, then DECLARE outside the BEGIN block.

DECLARE kk INT64;

BEGIN
  SET kk = (SELECT MAX(i) FROM `fh-bigquery.public_dump.numbers_255`);
  SELECT 1/0;
EXCEPTION WHEN ERROR THEN
  SELECT kk;
END;