Create Snowflake outer query around stored procedure execution

I am trying to create a Snowflake query that wraps a stored procedure CALL statement and applies additional logic to the results.

The Snowflake documentation states "you cannot use a stored procedure CALL as part of an expression." but was wondering if there was a way to accomplish this.

Here is an example of what I need done (this does not work).

SELECT * FROM (CALL SP_REPORTDATA(('employeeId = 12345) OR (employeeId = 23456)') WHERE 'employeeId' = 12345

The correct way to get the results of a stored procedure is to use RESULT_SCAN

Thus for you code it would be two SQL commands:

CALL SP_REPORTDATA('(employeeId = 12345) OR (employeeId = 23456)');

SELECT * FROM table(RESULT_SCAN(LAST_QUERY_ID()))
WHERE employeeId = 12345;

assuming your stored proc returned a table of results:

The following is pretty much a verbatim of the doc's Using an Argument When Building a String for a SQL Statement

create table question_data(employeeId number, data text);
insert into question_data values 
    (12345, 'extra 1'), 
    (23456, 'extra 2'),
    (34567, 'extra 3');

CREATE OR REPLACE PROCEDURE SP_REPORTDATA (filter text)
    RETURNS TABLE (id number, extra_data text)
    LANGUAGE SQL
    AS
$$
    DECLARE
      res RESULTSET;
      sql TEXT;
    BEGIN
      sql := 'SELECT * FROM question_data WHERE ' || :filter;
      res := (EXECUTE IMMEDIATE :sql);
      RETURN TABLE(res);
    END;
$$;

then the call to your SP:

call SP_REPORTDATA('(employeeId = 12345) OR (employeeId = 23456)');

and the extra "work"

SELECT * FROM table(RESULT_SCAN(LAST_QUERY_ID()))
WHERE employeeId = 12345;