How to Create Stored Procedures in BigQuery

Galaxy Glossary

How do I create and use stored procedures in BigQuery?

BigQuery stored procedures are named, parameter-driven SQL blocks you store in a dataset and call to run repeatable logic.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What problem do BigQuery stored procedures solve?

Stored procedures let you package multi-statement SQL—joins, DML, and conditionals—into a single, versioned object. Teams stop pasting long scripts in Slack and reuse one trusted procedure.

How do I define a procedure that accepts parameters?

Use CREATE PROCEDURE with named parameters and declare the language as SQL. BigQuery automatically infers parameter modes as IN.

How do I call a stored procedure?

Use CALL dataset.proc_name(arg1, arg2);.Procedures run in the project where they are stored, and you are billed only for resources consumed by statements inside the procedure.

Can a procedure read and write ecommerce tables?

Yes. You can SELECT from Customers, INSERT into Orders, or UPDATE Products in the same procedure. Make sure the service account has the necessary dataset permissions.

How do I return results to the caller?

Finish with a SELECT statement.The result set is returned just like a regular query so it can feed downstream dashboards or scripts.

Best practices for production use?

• Prefix procedures with the team name, e.g., analytics.calculate_ltv.
• Keep each procedure focused on one job.
• Store code in version control and deploy via CI.
• Use OR REPLACE for zero-downtime updates.

How do I debug a procedure?

Add DECLARE variables and RAISE USING MESSAGE statements, or call the procedure inside the BigQuery UI with the Execution Details panel open for per-statement timings.

How do I secure procedures?

Grant the bigquery.jobs.create role to callers and use DEFINER security options to run with the procedure owner’s permissions when necessary.

Where are procedures stored?

They live inside a dataset next to tables and views.Export them via bq show --format=prettyjson for backups or migration.

What’s the billing impact?

Charges reflect the sum of bytes processed by all queries inside the procedure. Reading the same table twice counts twice, so refactor logic when costs spike.

When should I choose scripts over procedures?

Use scripts for ad-hoc exploration or one-off migrations. Convert to procedures once logic is stable and shared by more than one user or workflow.

.

Why How to Create Stored Procedures in BigQuery is important

How to Create Stored Procedures in BigQuery Example Usage


-- Stored procedure to restock a product if inventory is low
CREATE OR REPLACE PROCEDURE inventory.restock_product(
    p_product_id INT64,
    p_threshold INT64,
    p_restock_qty INT64
)
BEGIN
  DECLARE current_stock INT64;

  -- Check current stock
  SET (current_stock) = (
    SELECT stock FROM `ecommerce.Products` WHERE id = p_product_id
  );

  -- Restock when needed
  IF current_stock < p_threshold THEN
    UPDATE `ecommerce.Products`
    SET stock = stock + p_restock_qty
    WHERE id = p_product_id;
  END IF;

  -- Return the new stock level
  SELECT id, name, stock FROM `ecommerce.Products` WHERE id = p_product_id;
END;

-- Call it
CALL inventory.restock_product(10, 20, 100);

How to Create Stored Procedures in BigQuery Syntax


CREATE OR REPLACE PROCEDURE analytics.calculate_customer_ltv(
    customer_id INT64
)
BEGIN
  -- Step 1: aggregate order totals
  CREATE OR REPLACE TEMP TABLE tmp_totals AS
  SELECT SUM(total_amount) AS lifetime_value
  FROM `ecommerce.Orders`
  WHERE customer_id = customer_id;

  -- Step 2: return result
  SELECT lifetime_value FROM tmp_totals;
END;

-- Execute
CALL analytics.calculate_customer_ltv(42);

Common Mistakes

Forgetting to end the procedure with a SELECT, which leads to an empty result set. Always return data or use OUT parameters when the caller expects output.
Omitting project or dataset prefixes, causing the procedure to reference wrong tables. Qualify objects with full paths like `project.dataset.table`.

Frequently Asked Questions (FAQs)

Can I call a BigQuery procedure from Airflow?

Yes. Use the BigQueryInsertJobOperator with a CALL statement. The task waits until the procedure finishes.

Do procedures support transactions?

BigQuery executes each DML statement atomically, but it does not support multi-statement transactional rollbacks. Design idempotent logic instead.

How do I migrate procedures between projects?

Export definitions with bq show --format=prettyjson, store them in Git, and deploy via bq query --use_legacy_sql=false in the target project.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie