NULL (Snowflake Scripting)

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

Note

Using NULL as a statement is uncommon. NULL is usually used as a value, rather than as a statement.

As a value, NULL means “no value”. For more information, see the Wikipedia article on SQL NULL.

When working with semi-structured data types, such as JSON, you might need to distinguish between NULL as an SQL value and NULL as a JSON value (also called “variant null”).

Note

This Snowflake Scripting construct is valid only within a Snowflake Scripting block.

Syntax

NULL;
Copy

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
$$
BEGIN
    SELECT 1 / 0;
    RETURN 'If you see this, the exception was not thrown/caught properly.';
EXCEPTION
    WHEN OTHER THEN
        NULL;
END;
$$
;
Copy
CALL null_as_statement();
+-------------------+
| NULL_AS_STATEMENT |
|-------------------|
|              NULL |
+-------------------+
Copy

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.

Language: English