How to write an equivalent IF ELSE adhoc sql query in Snowflake

Question

I am trying to create a Snowflake equivalent to the below T-SQL based adhoc query.

**T-SQL version**
Declare @i int = 0;

If(@i = 0)
   PRINT '0';
Else
 Begin
   PRINT '1'
   RETURN;
   PRINT '2'
 End


**Snowflake version**
Set i = 0;

If($i = 0)
   Select '0';
Else
 Begin
   Select '1'
   RETURN;
   Select '2'
 End

When I am running the Snowflake query from the Snowflake Web UI, I am getting an error as SQL compilation error : syntax error line 1 at position 0 unexpected 'IF'

I have searched the snowflake documentation and did not find helpful documentation for:

If Else
Begin End
Return

Any help would be appreciated.

Thank you.

Unknown Unknown · Accepted Answer · 2020-06-23T01:01:55

Snowflake does not support conditional T-SQL statements. It does support conditional expressions within a query.

For conditional statement execution, consider using Snowflake's stored procedures with embedded JavaScript if the use of conditional expressions is inadequate for your needs.

A very trivial example of a stored procedure, illustrating a conditional query execution upon call:

CREATE OR REPLACE PROCEDURE stored_proc_example(TEST_VALUE INTEGER)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    // Declaring SQL statement(s) to execute later
    var command_1 = "SELECT 1";
    var command_2 = "SELECT 2";

    if (TEST_VALUE === 0) {

        var stmt = snowflake.createStatement({ sqlText: command_1 });
        var rs = stmt.execute();
        // Omitted: Perform logic with 'rs' rows to prepare a return value
        return '1';

    } else {

        var stmt = snowflake.createStatement({ sqlText: command_2 });
        var rs = stmt.execute();
        // Omitted: Perform logic with 'rs' rows to prepare a return value
        return '2';

    }
$$
;

Run as:

SET i=0;
CALL stored_proc_example($i);

SET i=1;
CALL stored_proc_example($i);

How to write an equivalent IF ELSE adhoc sql query in Snowflake

2 Answers