NULL (Snowflake Scripting) | Snowflake Documentation (original) (raw)

ReferenceScripting referenceNULL

NULL can be used as a “no-op” (no operation) statement.

Syntax¶

Usage notes¶

• The NULL statement can be executed only inside Snowflake Scripting scripts.
• A NULL statement in an exception handler ensures that the code continues executing rather than aborting if there is no higher-level handler.
• A NULL statement in a branch does nothing; however, it communicates to the reader that the author of the code explicitly considered the condition for which the branch would execute. In other words, the NULL shows that the branch condition was not overlooked or accidentally omitted.
• Before using the NULL statement, consider alternatives.
  For example, suppose you are writing a stored procedure with an exception handler. In most stored procedures, if each non-exception code path should return a value, then each code path involving an exception handler should also return a value. In that case, avoid executing a NULL statement. Instead, consider explicitly returning NULL, an empty result set, or an error indicator.

Example¶

The following code uses a NULL statement in an exception handler to ensure that the exception is caught (rather than passed up to the caller), but no specific action is taken:

CREATE PROCEDURE null_as_statement() RETURNS VARCHAR LANGUAGE SQL AS BEGINSELECT1/0;RETURN′Ifyouseethis,theexceptionwasnotthrown/caughtproperly.′;EXCEPTIONWHENOTHERTHENNULL;END;BEGIN SELECT 1 / 0; RETURN 'If you see this, the exception was not thrown/caught properly.'; EXCEPTION WHEN OTHER THEN NULL; END;BEGINSELECT1/0;RETURN′Ifyouseethis,theexceptionwasnotthrown/caughtproperly.′;EXCEPTIONWHENOTHERTHENNULL;END; ;

CALL null_as_statement(); +-------------------+

Note

The NULL value returned by the CALL statement is not directly due to the NULL statement in the exception; instead, the return value is NULL because the stored procedure did not execute an explicit RETURN statement.

Snowflake recommends that stored procedures explicitly return a value, including in each branch of the exception handler.