Dynamic SQL: EXECUTE IMMEDIATE and FOR

Reports sometimes need SQL that only exists once the script is running, such as a table named in a variable, or one statement repeated for each row of a query. The EXECUTE IMMEDIATE statement runs such SQL, and a FOR loop visits the rows of a query.

How it works

EXECUTE IMMEDIATE takes a string and runs it as a statement of its own. That statement sees tables, but it cannot read the variables you DECLARE. Values reach it through USING, which fills the ? or @name placeholders in the text, and a one-row result comes back through INTO, which fills variables. A table or column name has to be written into the text before it runs. FOR row IN (query) DO ... END FOR runs its statements once for each row of the query, and row.column reads that row.

Common mistake

Building a whole statement by pasting values into text works until a value holds a quote, and it hands the writer of that value control over the statement. Paste only names that your own script chose or has checked. A query you can write down in advance is still simpler than dynamic SQL.

Worked example

DECLARE group_column STRING DEFAULT 'fuel_type';
DECLARE result STRING DEFAULT 'FAILED';
DECLARE failed_tests INT64;

CREATE TEMP TABLE ford_january AS
SELECT test_result, vehicle_snapshot.fuel_type AS fuel_type, vehicle_snapshot.primary_colour AS colour
FROM `learn.mot.fact_mot_test`
WHERE make = 'FORD'
  AND completed_date BETWEEN '2024-01-01' AND '2024-01-31';

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ford_january WHERE test_result = ?'
  INTO failed_tests
  USING result;

EXECUTE IMMEDIATE FORMAT(
  'SELECT %s AS value, COUNT(*) AS tests, ROUND(COUNT(*) / ?, 3) AS share FROM ford_january WHERE test_result = ? GROUP BY value ORDER BY tests DESC, value',
  group_column
)
  USING failed_tests, result;

The script keeps the January tests of one make in the temp table ford_january. Its variables hold the column to group by and the result to look for.

The first EXECUTE IMMEDIATE counts the failed tests. The ? takes the value of result from USING, and INTO stores the single row in failed_tests.

The second one groups by a column whose name is in a variable. FORMAT writes fuel_type into the text at the %s, because a parameter cannot name a column. The two ? take failed_tests and result in the order they appear in the text. It is the last statement of the script, so its rows are the result.

The colour column is there so that group_column could name another column. Setting group_column to colour groups by colour with no other change.

All BigQuery lessons