EXEC Helper
EXEC function is a helper used to execute dynamic SQL inside a procedure.
You might also be interested in:
EXEC helper depends on IS NULL helper.
Syntax
EXEC(stmt) EXEC(stmt, binds[]) EXEC(stmt, opts{}) EXEC(stmt, binds[], opts{})
Parameters
stmt
The string of the SQL statement to execute.
binds (optional)
An array with the values or the variables to bind into the SQL statement.
opts (optional)
This is a Javascript object to describe how the values returned by the exec should be formated, this is used for SELECT statements.
Valid arguments for opts parameter
The following tables describe, how arguments should be sent to opts parameter in EXEC call:
Options when a query returns a single row
opts | description |
---|---|
{ } | When opts is empty or not sent to exec call, the data will be returned inside an array. |
{vars: 0} | This has the same effect as the default option. It will return the data inside an array. |
{vars: 1} | This is used when a query returns just one column and one row. EXEC will return the value directly. This is equivalent to EXEC(stmt)[0] |
{rec:recordVariable} | Used when you want to store the values returned by the query inside a record. Translation of records is described in Records translation reference. Record variable should be passed as an argument. |
{row: 1} | This option returns a copy of ResultSet, this means that the object returned contains the methods described in ResultSet Snowflake documentation. |
Options when a query returns multiple rows
opts | Description |
---|---|
{row:2} | With this option, it always returns a copy of the ResultSet regardless of the number of rows returned by the EXEC. |
General options
opts | Description |
---|---|
{sql:0} | It makes sure that the SQL implicit Cursor attribute is not modified after executing the statement. |
EXEC Helper Function Definition
Usage Samples
For all the samples, SnowConvert helpers Code were removed. You can find them here.
The following code examples illustrates how EXEC works.
EXEC simple case
Oracle