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

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