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

user_3365490 0
Viewed 12191

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:

  1. If Else
  2. Begin End
  3. Return

Any help would be appreciated.

Thank you.

snowflake-cloud-data-platform return if-statement
4 Answers

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);
Admin1

Snowflake Scripting introduced anonymous block and Branching constructs:

DECLARE 
  i INT DEFAULT 0;
BEGIN
  IF (i = 0) THEN
     RETURN 'zero';
  ELSE
     RETURN 'non-zero';
  END IF;
END;

or:

BEGIN
  LET i INT := 0;
   
  IF (i = 0) THEN
     RETURN 'zero';
  ELSE
     RETURN 'non-zero';
  END IF;
END;

Further reading: Working with Variables

user_50708790
user_118551150

You can try with snowflake scripting and change your accordingly

set
    stmt = $$
 begin
    let i := 1;
case when (i=0) then 
    return 0;
 when (i=1 ) then 
    return '1 ';
    else 
    return '2';
 end ;
end;
$$;
execute immediate $stmt;
user_144426640
Related