Returning a value¶

Types of return values¶

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

• A SQL data type

• 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)
    ...
  

  Copy

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

  Copy

  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
  

  Copy

  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()
    ...
  

  Copy

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

  Copy

  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);
  ...
  

  Copy

  See Returning a RESULTSET as a table.

Setting a variable to the return value of a stored procedure¶

See Using the value returned from a stored procedure call.