Returning a Value

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

  • A block in a stored procedure.

  • An anonymous block.

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 in CREATE PROCEDURE, you cannot specify GEOGRAPHY as a column type.

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

    If you do so, calling the stored procedure results in the error:

    CALL test_return_geography_table_1();
    
    Copy
    Stored procedure execution error: data type of returned table does not match expected returned table type
    
    Copy

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

    CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
    RETURNS TABLE()
    ...
    
    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.