Snowflake: Is it mandatory to return value from procedure?

user_6559160 0
Viewed 1147

https://docs.snowflake.com/en/sql-reference/stored-procedures-overview.html#returning-a-value-from-a-stored-procedure-is-optional

Snowflake doc says A stored procedure is allowed to return a value, but is not required to return a value.

But in syntax RETURNS is mandatory.

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS <result_data_type> [ NOT NULL ]
  LANGUAGE JAVASCRIPT
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]
  AS '<procedure_definition>'

Can someone explain how can we create a procedure without returning a value out of it or is it mandatory to return a value from procedure?

snowflake-cloud-data-platform
1 Answers

According to that syntax it is mandatory. But your returning value can be just static varchar and not coming from your query result. Or even NULL if you don't return anything from your procedure.

For example like this (returning NULL)

CREATE OR REPLACE PROCEDURE testproc()
RETURNS varchar
LANGUAGE JAVASCRIPT AS
'var sqlcommand =
`INSERT INTO foo (bar) VALUES (999);`
snowflake.execute({sqlText: sqlcommand});
';

But it is better to add some error handling and return success / failure message, like this:

CREATE OR REPLACE PROCEDURE testproc()
RETURNS varchar
LANGUAGE JAVASCRIPT AS
'var sqlcommand =
`INSERT INTO foo (bar) VALUES (999);`
try {
    snowflake.execute({sqlText: sqlcommand});
    return "Success";
    }
catch(err) {
    return "Failed: "+ err;
}
';
user_27409280
Related