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

CREATE OR REPLACE PROCEDURE test.proc1 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
    RETURN 'SUCCESS';
END;
$$;

IN Parameter Mode

BigQuery

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

Snowflake

CREATE OR REPLACE PROCEDURE test.proc2 (p1 INT)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  BEGIN
    INSERT INTO test.tableProc
    VALUES (:p1);
  END;
$$;

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

CREATE OR REPLACE PROCEDURE test.proc1 (number INT, rows_added INT)
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
    CREATE OR REPLACE TEMP TABLE DataForTargetDate AS
    SELECT * 
    FROM test.tableProc WHERE COL1 < :number;

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

    SELECT *
    FROM DataForTargetDate;

    DROP TABLE DataForTargetDate;
    RETURN rows_added;
END;
$$;

CREATE OR REPLACE PROCEDURE test.proc2 (p1 INTEGER, p2 INTEGER, p3 INTEGER)
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF (:p3 > 0 OR :p2 > 40) THEN
         p1 := 2;
         p2 := 1000;
         RETURN OBJECT_CONSTRUCT('p1', :p1, 'p2', :p2);
      END IF;
         p1 := 5;
         p2 := 3000;
         RETURN OBJECT_CONSTRUCT('p1', :p1, 'p2', :p2);
   END;
$$;

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

CREATE OR REPLACE PROCEDURE test.proc1 (x INT, delta INT)
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
    SET x := x + delta
    RETURN x;
END;
$$;

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

CREATE OR REPLACE PROCEDURE test.proc1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = 'A procedure that runs a query.'
EXECUTE AS CALLER
AS
$$
BEGIN
    RETURN 'SUCCESS';
END;
$$;