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.
EXECUTE IMMEDIATE 'SELECT ... WHERE make = ?' USING chosen_make;runs the text with the value ofchosen_makewhere the?is. When it is the last statement of the script, the rows it returns are the result of the script.- A value with no name fills a
?, in order:USING a, bputsain the first?andbin the second. A value with a name,USING a AS make, fills@make. Use one style in a statement, because mixing the two fails. - A parameter is a value and BigQuery never reads it as SQL. A value pasted into the text is read as SQL, so a name like
O'Briencloses the quoted string early and the statement no longer parses. Pass every value withUSING. - A parameter replaces a value and nothing else.
FROM ?fails withQuery parameters cannot be used in place of table names, and a?in a select list is a constant. A table or column name has to be built into the text, for example withFORMAT('SELECT * FROM %s', table_name). The name is pasted in as SQL, so check it against a fixed list withASSERTfirst when it comes from outside the script. INTO a, bstores the single row of the query in the variablesaandb, one variable for each column in order. The query must return no more than one row. With no row the variables becomeNULL, whatever they held before. A different number of columns than variables fails.LIMIT 1or an aggregate keeps a query to one row.- The text cannot read a variable that you DECLARE:
WHERE type = chosen_typefails withUnrecognized name: chosen_type. A temp table is visible to the text, because it belongs to the script. This lesson reads temp tables, so the text can name them without a dataset. FOR row IN (SELECT ...) DO ... END FOR;reads its query once and runs the statements for each row, in the order of the query. Write anORDER BYwhen the order matters.rowhas one field for each column, sorow.makeis the make of the current row.- A pass that reads a table is billed like any statement, with a minimum of 10 MiB for each table read. A loop of three passes is billed for three reads, plus the one for its own query. A query that returns all the rows at once is billed once.
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.