sqlsnowflake-cloud-data-platformsnowflake-schema

How to join the Snowflake stored procedure result with a table


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?


Solution

  • 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;
    $$;
    
    1. call it
    call first_proc();
    
    1. fetch the results via RESULT_SCAN and LAST_QUERY_ID
    select * from table(result_scan(last_query_id()));
    
    1. profit!

    enter image description here