Selecting from a stored procedure

Some stored procedures return tabular data. To select and manipulate this tabular data, you can call these stored procedures in the FROM clause of a SELECT statement.

Run a SELECT statement with the TABLE keyword

When calling the stored procedure, omit the CALL command. Instead, use the TABLE keyword, and name the procedure inside parentheses:

SELECT ... FROM TABLE( <stored_procedure_name>( <arg> [ , <arg> ... ] ) );
Copy

Example that selects from a stored procedure

This example uses the data in the following table:

CREATE OR REPLACE TABLE orders (
  order_id INT,
  u_id VARCHAR,
  order_date DATE,
  order_amount NUMBER(12,2));

INSERT INTO orders VALUES (1, 'user_id_001', current_date, 500.00);
INSERT INTO orders VALUES (2, 'user_id_003', current_date, 225.00);
INSERT INTO orders VALUES (3, 'user_id_001', current_date, 725.00);
INSERT INTO orders VALUES (4, 'user_id_002', current_date, 150.00);
INSERT INTO orders VALUES (5, 'user_id_002', current_date, 900.00);
Copy

The following stored procedure returns order information based on a user ID:

CREATE OR REPLACE PROCEDURE find_orders_by_user_id(user_id VARCHAR)
RETURNS TABLE (
  order_id INT, order_date DATE, order_amount NUMBER(12,2)
)
LANGUAGE SQL AS
DECLARE
  res RESULTSET;
BEGIN
  res := (SELECT order_id, order_date, order_amount FROM orders WHERE u_id = :user_id);
  RETURN TABLE(res);
END;
Copy

Note: If you are using 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 SnowSQL, the Classic Console, and Python Connector):

CREATE OR REPLACE PROCEDURE find_orders_by_user_id(user_id VARCHAR)
RETURNS TABLE (
  order_id INT, order_date DATE, order_amount NUMBER(12,2)
)
LANGUAGE SQL AS
$$
DECLARE
  res RESULTSET;
BEGIN
  res := (SELECT order_id, order_date, order_amount FROM orders WHERE u_id = :user_id);
  RETURN TABLE(res);
END;
$$
;
Copy

The following SELECT statement retrieves the stored procedure’s results:

SELECT * FROM TABLE(find_orders_by_user_id('user_id_001'));
Copy
+----------+------------+--------------+
| ORDER_ID | ORDER_DATE | ORDER_AMOUNT |
|----------+------------+--------------|
|        1 | 2024-08-30 |       500.00 |
|        3 | 2024-08-30 |       725.00 |
+----------+------------+--------------+

Limitations for selecting from a stored procedure

The following limitations apply to selecting from a stored procedure:

  • Only stored procedures that perform SELECT, SHOW, DESCRIBE, or CALL statements can be placed in the FROM clause of a SELECT statement. Stored procedures that make modifications using DDL or DML operations aren’t allowed. For stored procedures that issue CALL statements, these limitations apply to the stored procedures that are called.

  • Only stored procedures that return tabular data with a static output schema can be placed in the FROM clause of a SELECT statement. The output columns must be named and typed. For example, a stored procedure with the following RETURNS clause is supported:

    RETURNS TABLE (col1 INT, col2 STRING)
    Copy

    A stored procedure with the following RETURNS clause is not supported because it doesn’t return tabular data:

    RETURNS STRING
    Copy

    A stored procedure with the following RETURNS clause is not supported because because it doesn’t provide a fixed output schema:

    RETURNS TABLE()
    Copy

  • The stored procedure must be called in the FROM clause of a SELECT block in one of the following statements:

  • The stored procedure can’t accept correlated input arguments from their outer scope, such as a reference to any CTE defined outside of the SELECT statement.

  • If an argument contains a subquery, then that subquery can’t use a CTE defined by the WITH clause.

  • A SELECT statement containing a stored procedure call can’t be used in the body of a view, a user-defined function (UDF), a user-defined table function (UDTF), or in objects such as row access policies and data masking policies.

Language: English