SET

Sets a variable to have the value of the provided expression, or sets multiple variables at the same time based on the result of multiple expressions.

Grammar Syntax

SET variable_name = expression;
SET (variable_name[, ...]) = (expression[, ...]);

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

Sample Source Patterns

Basic case

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
    x := x + delta;
    RETURN x;
END;
$$;

Multiple variables

BigQuery

CREATE OR REPLACE PROCEDURE test.proc1(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.proc3 (p1 INT, p2 INT, p3 INT)
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
    IF (p3 > 0) 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;
$$;

System variables

BigQuery

DECLARE var1 STRING;
BEGIN
    SET var1 = "value1";
END;
INSERT INTO TABLE1 VALUES (var1);

Snowflake

SET var1 = NULL;
BEGIN
    SET var1 = 'value1';
END;
INSERT INTO TABLE1
VALUES ($var1);