Returning a value

To return a value, use the RETURN command. You can return a value from the following items:

Types of return values

You can return a value of one of the following types:

  • A table

    Use TABLE(...) in the RETURN statement.

    If your block is in a stored procedure, you must also specify the RETURNS TABLE(...) clause in the CREATE PROCEDURE statement.

    Note

    Currently, in the RETURNS TABLE(...) clause, you can’t specify GEOGRAPHY as a column type. This applies whether you are creating a stored or anonymous procedure.

    CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
  RETURNS TABLE(g GEOGRAPHY)
  ...

    WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE(g GEOGRAPHY)
  ...
CALL test_return_geography_table_1();

    If you attempt to specify GEOGRAPHY as a column type, calling the stored procedure results in the error:

    Stored procedure execution error: data type of returned table does not match expected returned table type

    To work around this issue, you can omit the column arguments and types in RETURNS TABLE().

    CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
  RETURNS TABLE()
  ...

    WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE()
  ...
CALL test_return_geography_table_1();

    If you want to return the data that a RESULTSET points to, pass the RESULTSET to TABLE(...), as shown in the example below:

    CREATE PROCEDURE ...
RETURNS TABLE(...)
...
RETURN TABLE(my_result_set);
...

    See Returning a RESULTSET as a table.

Returning the value of a variable

This example declares a variable named my_var for use in a Snowflake Scripting anonymous block and then returns the value of the variable:

DECLARE
  my_var VARCHAR;
BEGIN
  my_var := 'Snowflake';
  RETURN my_var;
END;

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, and Python Connector):

EXECUTE IMMEDIATE
$$
DECLARE
  my_var VARCHAR;
BEGIN
  my_var := 'Snowflake';
  RETURN my_var;
END;
$$;

Using the value returned from a stored procedure call

See Using the value returned from a stored procedure call.

Using the value returned from a Snowflake Scripting UDF

See Snowflake Scripting UDFs.