CASE

Executes the THEN sql_statement_list where the boolean expression is true, or the optional ELSE sql_statement_list if no conditions match.

Grammar Syntax

CASE
  WHEN boolean_expression THEN sql_statement_list
  [...]
  [ELSE sql_statement_list]
END CASE;

--CASE search_expression
CASE search_expression
  WHEN expression THEN sql_statement_list
  [...]
  [ELSE sql_statement_list]
END CASE;

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

The grammar is fully supported by Snowflake.

Sample Source Patterns

Simple CASE

BigQuery

CREATE OR REPLACE PROCEDURE test.proc1(OUT result STRING)
BEGIN 
DECLARE target_product_id INT64 DEFAULT 103;
CASE
  WHEN
    EXISTS(SELECT 1 FROM test.products_a WHERE product_id = target_product_id)
    THEN SET result = 'found product in products_a table';
  WHEN
    EXISTS(SELECT 1 FROM test.products_b WHERE product_id = target_product_id)
    THEN SET result = 'found product in products_b table';
  ELSE
    SET result = 'did not find product';
    INSERT INTO test.tableProc VALUES (target_product_id);
END CASE;
END;

Snowflake

CREATE OR REPLACE PROCEDURE test.proc1 (result STRING)
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE target_product_id INT DEFAULT 103;
  BEGIN
    CASE
      WHEN
      EXISTS(SELECT 1 FROM
          test.products_a
        WHERE product_id = :target_product_id
      ) THEN
        result := 'found product in products_a table';
      WHEN
      EXISTS(SELECT 1 FROM
          test.products_b
        WHERE product_id = :target_product_id
      ) THEN
        result := 'found product in products_b table';
    ELSE
        result := 'did not find product';
      INSERT INTO test.tableProc
        VALUES (:target_product_id);
    END CASE;
    RETURN result;
  END;
$$;

CASE search_expression

BigQuery

CREATE OR REPLACE PROCEDURE test.proc1(product_id INT64, OUT id STRING)
BEGIN 
CASE product_id
  WHEN 1 THEN
    SET id = 'Product one';
  WHEN 2 THEN
    SET id = 'Product two';
  ELSE
    SET id = 'Invalid product';
END CASE;
END;

Snowflake

CREATE OR REPLACE PROCEDURE test.proc1 (product_id INT, id STRING)
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
    CASE product_id
      WHEN 1 THEN
        id := 'Product one';
      WHEN 2 THEN
        id := 'Product two';
       ELSE
        id := 'Invalid product';
    END CASE;
    RETURN id;
END;
$$;