A database contains a table and a stored procedure defined as:
The log_table is initially empty and a Data Engineer issues the following command:
CALL insert_log(NULL::VARCHAR);
No other operations are affecting the log_table.
What will be the outcome of the procedure call?
If the stored procedure is defined with RETURNS NULL ON NULL INPUT, it means that the procedure will not execute if the input is NULL. Therefore, the SQL statement within the procedure will not run, and no records will be inserted into the log_table. Consequently, log_table will remain empty. However, the stored procedure is designed to return a value of 1 regardless of whether or not the input is NULL, as indicated in the 'return 1;' statement at the end of the procedure. Thus, the log_table will contain zero records, and the stored procedure will return 1 as the return value.
in the documentation, it says: RETURNS NULL ON NULL INPUT will not call the stored procedure if any input is null, so the statements inside the stored procedure will not be executed.
"return null on null input"
RETURNS NULL ON NULL INPUT (or its synonym STRICT) will not call the UDF if any input is null. Instead, a null value will always be returned for that row. Note that the UDF might still return null for non-null inputs. see: https://docs.snowflake.com/en/sql-reference/sql/create-function
why does the log table contain no records? Wouldn't the SQL statement execute and one record with a null value be inserted?