CREATE PROCEDURE

A stored procedure is a collection of statements that can be called from other queries or other stored procedures.

Grammar Syntax for SQL procedures

CREATE [OR REPLACE] PROCEDURE [IF NOT EXISTS]
[[project_name.]dataset_name.]procedure_name (procedure_argument[, ...] )
[OPTIONS(procedure_option_list)]
BEGIN
multi_statement_query
END;

procedure_argument: [procedure_argument_mode] argument_name argument_type

procedure_argument_mode: IN | OUT | INOUT

Click here to go to the BigQuery specification for this syntax.

Sample Source Patterns

Basic case

BigQuery

CREATE OR REPLACE PROCEDURE test.proc1()
BEGIN
  RETURN;
END;

Snowflake

IN Parameter Mode

BigQuery

CREATE OR REPLACE PROCEDURE test.proc1(IN p1 INT64)
BEGIN
  INSERT INTO test.tableProc VALUES (p1);
END;

Snowflake

IN mode parameter is removed, not required in Snowflake.

OUT Parameter Mode

BigQuery

CREATE OR REPLACE PROCEDURE test.proc1(number INT64, OUT rows_added INT64)
BEGIN
  CREATE OR REPLACE TEMP TABLE DataForTargetDate AS
  SELECT * 
  FROM test.tableProc WHERE COL1 < number;

  SET rows_added = (SELECT COUNT(*) FROM DataForTargetDate);

  SELECT *
  FROM DataForTargetDate;

  DROP TABLE DataForTargetDate;
END;

CREATE OR REPLACE PROCEDURE test.proc2(OUT p1 INT64, OUT p2 INT64, p3 INT64)
BEGIN
  IF p3 > 0 THEN
    SET (p1, p2) = (2, 1000);
    RETURN;
  END IF;
  SET (p1, p2) = (5, 3000);
END;

Snowflake

Snowflake does not allow OUTPUT parameters in procedures, one way to simulate this behavior is return its value at the end of the procedure.

INOUT Parameter Mode

BigQuery

CREATE OR REPLACE PROCEDURE test.proc1(INOUT x INT64, delta INT64)
BEGIN
  SET x = x + delta;
END;

Snowflake

Same behavior as output parameters

Options Clause

BigQuery	Snowflake
strict_mode	Not supported in Snowflake, but by default the strict_mode is false.
description	COMMENT

Not supported options are removed from the output code.

BigQuery

CREATE OR REPLACE PROCEDURE test.proc1()
OPTIONS(
strict_mode = TRUE,
description = "A procedure that runs a query."
)
BEGIN
END;

Snowflake