AWAIT (Snowflake Scripting)¶

Waits for an asynchronous child job that is running for a RESULTSET, and returns when the job has finished.

AWAIT is a blocking call. You can use an AWAIT statement to block other code from running until an asynchronous child job has finished and its results are returned.

Note

This Snowflake Scripting construct is valid only within a Snowflake Scripting block.

See also:: CANCEL

Syntax¶

AWAIT <result_set_name> ;

Copy

Where:

result_set_name
The name of the RESULTSET.

Usage notes¶

An asynchronous child job is created for a RESULTSET when the ASYNC keyword is specified for the query. For more information, see Assigning a query to a declared RESULTSET.
When the ASYNC keyword is specified for a query, the stored procedure can’t access the query results until an AWAIT statement for the RESULTSET returns the results.
When you run an asynchronous child job, “fire and forget” isn’t supported. Therefore, if the stored procedure runs a child job that is still running when the stored procedure completes, the child job is canceled automatically.
Snowflake Scripting supports built-in variables that you can use in the code for stored procedures. The SQLID variable is available for the query specified for an asynchronous child job immediately after the asynchronous child job is created.

All of the other built-in variables are populated after the AWAIT statement associated with the asynchronous child job runs. Before the AWAIT statement runs, these variables have NULL values.

Specifically, the following built-in variables for exception handling are available after the AWAIT statement associated with the asynchronous child job runs:
- SQLCODE
- SQLERRM
- SQLSTATE
The following built-in variables related to the number of rows affected by DML commands are also available after the AWAIT statement associated with the asynchronous child job runs:
- SQLROWCOUNT
- SQLFOUND
- SQLNOTFOUND
If an asynchronous child job fails, the AWAIT statement associated with the asynchronous job fails with an error, and execution of the stored procedure stops. For example, the following stored procedure fails and returns an error when execution reaches the AWAIT statement:
```
BEGIN
  LET res RESULTSET := ASYNC (SELECT * FROM invalid_table);
  AWAIT res;
END;
```
Copy
```
002003 (42S02): Uncaught exception of type 'STATEMENT_ERROR' on line 2 at position 4 : SQL compilation error:
Table 'INVALID_TABLE' does not exist or not authorized.
```

Examples¶

AWAIT my_result_set;

Copy