How to join the Snowflake stored procedure result with a table

Question

I'm trying to join the table resultset returned from a stored procedure to a table in snowflake.

The methods that I tried :

Method #1:

CREATE TEMPORARY TABLE temp_result AS (
    SELECT *
    FROM TABLE(execute_sql_from_sp())
);

Method #2:

WITH sp_result AS (
    SELECT *
    FROM TABLE(execute_sql_from_table())
)
SELECT t.column1, t.column2, sp_result.column3
FROM your_table t
JOIN sp_result ON t.id = sp_result.id;

None of these work. Any ideas on how we can achieve this?

Answer 1

As per the doc's Stored Procedure's results are not inline available:

So you have to do it indirectly.

1. The stored procedure:

CREATE OR REPLACE procedure FIRST_PROC()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT 
AS
$$
    A = 'DONE';
    return A;
$$;

2. call it

call first_proc();

3. fetch the results via RESULT_SCAN and LAST_QUERY_ID

select * from table(result_scan(last_query_id()));

4. profit!