Writing and running a stored procedure asynchronously *and* getting a return value

Suppose I have two stored procedures, foo and bar, both written using Snowflake Scripting language.

Inside foo, I want to call bar asynchronously and get a return value from bar. Is there currently a sane way to do that?

I have tried the following two ways, but both are invalid for one reason or another.

CREATE OR REPLACE TEMPORARY PROCEDURE bar(param1 NUMBER)
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS $$
BEGIN
  -- pretend there is some complex logic here before returning an object

  RETURN { 'status': 'OK' };
END;
$$;

CREATE OR REPLACE TEMPORARY PROCEDURE foo()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS $$
BEGIN
  -- pretend there is some complex logic here before I call `bar()`

  LET jobHandle := ASYNC (CALL bar(1));

  -- some more logic

  LET theThing := AWAIT jobHandle;

  RETURN theThing;
END;
$$;

For the above, I get this syntax error:

So I can't use LET, ASYNC and CALL together apparently.

How about "returning" via an OUT parameter?

CREATE OR REPLACE TEMPORARY PROCEDURE bar(param1 NUMBER, outObject OUT OBJECT)
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS $$
BEGIN
  -- pretend there is some complex logic here before returning an object

  outObject := { 'status': 'OK' };
END;
$$;

CREATE OR REPLACE TEMPORARY PROCEDURE foo()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS $$
DECLARE
  theThing OBJECT;
BEGIN
  -- pretend there is some complex logic here before I call `bar()`

  ASYNC (CALL bar(1, :theThing));

  -- some more logic

  AWAIT ALL;

  RETURN theThing;
END;
$$;

While the above does not result in a syntax error, I get an exception when I attempt to CALL foo():

There is a last resort that I can think of, and that is to create a table just for bar to store its results in, so that foo can retrieve the result after AWAITing bar, but that just seems... insane and convoluted.

It is possible to get result from async procedure:

CREATE OR REPLACE  PROCEDURE bar(param1 NUMBER)
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS 
BEGIN
  SELECT SYSTEM$WAIT(5);  -- simulate long operation
  RETURN { 'status': 'OK' };
END;

CREATE OR REPLACE  PROCEDURE foo()
RETURNS TABLE()
LANGUAGE SQL
EXECUTE AS CALLER
AS 
DECLARE
  jobHandle RESULTSET;
BEGIN
  jobHandle := ASYNC (CALL bar(1));
  AWAIT jobHandle;
  RETURN TABLE(jobHandle);
END;

CALL foo();

Output:

Alternatively using LET:

CREATE OR REPLACE  PROCEDURE foo()
RETURNS TABLE()
LANGUAGE SQL
EXECUTE AS CALLER
AS 
BEGIN
  LET jobHandle RESULTSET := ASYNC (CALL bar(1));
  AWAIT jobHandle;
  
  RETURN TABLE(jobHandle);
END;