Working with Stored Procedures

Stored Procedures enable:

  • Writing modular code so that the code is easier to understand and re-use.
  • Creating complex business logic by combining multiple SQL statements and procedural logic.

In this Topic:

Creating a Stored Procedure

This section provides more information about:

  • How to create a stored procedure.
  • Basic examples.
  • Special topics, such as transactions and stored procedures.
  • Stored procedure considerations (i.e. ways to avoid problems when writing stored procedures).

Stored Procedure DDL

Stored procedures are first-class database objects. The following DDL commands apply to stored procedures:

In addition, Snowflake provides the following command for executing stored procedures:

Implementation and API

Snowflake stored procedures are written in JavaScript. The JavaScript code can execute SQL statements by calling a JavaScript API. This API is similar to, but not identical to, the APIs in the various Snowflake connectors and drivers (Node.js, JDBC, Python, etc.):

  • The JavaScript provides error handling and procedural logic.
  • The SQL executed through the API provides database access.

The API enables you to perform operations such as:

  • Executing a SQL statement.
  • Retrieving the results of a query (i.e. a result set).
  • Retrieving metadata about the result set (number of columns, data types of the columns, etc.).

These operations are carried out by calling methods on the following objects:

  • snowflake, which has methods to create a Statement object and execute a SQL command.
  • Statement, which helps you execute prepared statements and access metadata for those prepared statements, and allows you to get back a ResultSet object.
  • ResultSet, which holds the results of a query (e.g. the rows of data retrieved for a SELECT statement).
  • SfDate, which is an extension of JavaScript Date (with additional methods) and serves as a return type for the Snowflake SQL data types TIMESTAMP_LTZ, TIMESTAMP_NTZ, and TIMESTAMP_TZ.

These objects are described in detail in the Stored Procedures API.

A typical stored procedure might contain code that looks similar to the following pseudo-code:

var my_sql_command1 = "delete from history_table where event_year < 2016";
var statement1 = snowflake.createStatement(my_sql_command1);
statement1.execute();

var my_sql_command2 = "delete from log_table where event_year < 2016";
var statement2 = snowflake.createStatement(my_sql_command2);
statement2.execute();

In a more realistic example, the year would be a parameter rather than hard-coded.

This code uses an object named snowflake, which is a special object that exists without being declared. The object is provided inside the context of each stored procedure and exposes an API to allow you to interact with the server.

The other variables (e.g. statement1) are created with JavaScript var statements. For example:

var statement1 = ...;

As shown in the code sample above, the snowflake object allows you to create a Statement object by calling one of the methods in the API.

Here’s another pseudo-code example that retrieves a ResultSet and iterates through it:

var my_sql_command = "select * from table1";
var statement1 = snowflake.createStatement(my_sql_command);
var result_set1 = statement1.execute();
// Loop through the results, processing one row at a time...
while (result_set1.next())  {
   var column1 = result_set1.getColumnValue(1);
   var column2 = result_set1.getColumnValue(2);
   // Do something with the retrieved values...
   }

The Examples section (at the end of this topic) provides additional examples that exercise each of the objects, and many of the methods, in the stored procedure JavaScript API.

SQL and JavaScript Data Type Mapping

When calling, using, and getting values back from stored procedures, you’ll often need to convert from a Snowflake SQL data type to a JavaScript data type or vice versa.

SQL to JavaScript conversion can occur when:

  • Calling a stored procedure with an argument. The argument is an SQL data type, and typically will be stored inside a JavaScript variable inside the stored procedure, so it must be converted.
  • When retrieving a value from a ResultSet object into a JavaScript variable. The ResultSet holds the value as an SQL data type, and the JavaScript variable must store the value as one of the JavaScript data types.

JavaScript to SQL conversion can occur when:

  • Returning a value from the stored procedure. The return statement typically contains a JavaScript variable that must be converted to an SQL data type.
  • When dynamically constructing an SQL statement that uses a value in a JavaScript variable.
  • When binding a JavaScript variable’s value to a PreparedStatement.

The sections below explain how data is converted from SQL to JavaScript or from JavaScript to SQL.

Converting from SQL to JavaScript

The following table shows the JavaScript data types that correspond to each of the supported Snowflake SQL data types:

SQL Data Type JavaScript Data Type Footnotes
ARRAY JSON  
BOOLEAN boolean  
DATE date  
REAL (FLOAT, FLOAT8, FLOAT4, DOUBLE, DOUBLE PRECISION, REAL) number  
TIME string  
TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ SfDate 1, 2.
VARCHAR (CHAR, CHARACTER, STRING, TEXT) string  
VARIANT JSON  

Footnotes:

  1. For more details about the SfDate data type, which is not a standard JavaScript data type, see the Stored Procedures API).
  2. When a TIMESTAMP is passed as an argument to a stored procedure, it is converted to a JavaScript date object. In other situations (for example, when retrieving from a ResultSet), a TIMESTAMP is converted to an SfDate object.

For example, if your SQL statement selects a VARIANT column from a Snowflake table, then when you copy the value from the ResultSet into a JavaScript variable, the JavaScript variable must be of type JSON.

Not all Snowflake SQL data types have an exact corresponding JavaScript data type. For example, JavaScript does not directly support INTEGER or NUMERIC data types. In those cases, convert the SQL data type to an appropriate alternative data type. For example, you might convert an SQL INTEGER into an SQL FLOAT, which can then be converted to a JavaScript value of type number.

The table below shows appropriate conversions for some SQL data types:

Incompatible SQL Data Type Compatible Data Type
INTEGER FLOAT
NUMBER, NUMERIC, DECIMAL FLOAT

Converting from JavaScript to SQL

The return type of a stored procedure is declared as part of the stored procedure definition. If the return statement in the JavaScript returns a data type different from the stored procedure’s declared return type, the JavaScript value is cast to the SQL data type if possible. For example, if a number is returned, but the stored procedure is declared as returning a string, the number is converted to a string within JavaScript, and then copied to the string returned in the SQL statement. (Keep in mind that some JavaScript programming errors, such as returning the wrong type, can be hidden by this behavior.)

If no valid cast for the conversion exists, then an error occurs.

Additional Information about Data Type Conversion

You might also find the following helpful:

This information about data types, capitalization of argument names, and NULL values applies to stored procedures, as well as to user-defined functions (UDFs).

For information about Snowflake SQL data types, see: Summary of Data Types.

Session State

As with any other SQL statement, a CALL statement runs within a session, and inherits context from that session, such as session-level variables, current database, etc.

Similarly, changes that the stored procedure makes to the session can persist after the end of the CALL.

The exact context that the procedure inherits depends upon whether the stored procedure is a caller’s rights procedure or an owner’s rights procedure. The exact changes that remain after the procedure was called also depend upon whether the stored procedure was an owner’s rights or a caller’s rights stored procedure.

Caller’s Rights Stored Procedures

This section assumes that you are using a caller’s rights stored procedure.

For example, suppose that the stored procedure named MyProcedure executes SQL statements that read and set session-level variables. In this example, the details of the read and set commands are not important, so the statements are represented as pseudo-code:

  • READ_SESSION_VAR1
  • SET_SESSION_VAR2

You then execute the following sequence of statements in the same session:

SET SESSION_VAR1 = 'some interesting value';
CALL MyProcedure();   -- Executes READ_SESSION_VAR1 and uses the value of
                      -- SESSION_VAR1 in some way.
                      -- Sets the variable SESSION_VAR2, which is still
                      -- set after the procedure call has completed.
SELECT * FROM table WHERE column1 = $SESSION_VAR2;

This is equivalent to executing the following sequence:

SET SESSION_VAR1 = 'some interesting value';
READ_SESSION_VAR1;
SET_SESSION_VAR2;
SELECT * FROM table WHERE column1 = $SESSION_VAR2;

In other words, the stored procedure can see the variable that was set by statements before the procedure was called, and the statements after the stored procedure can see the variables that were set inside the procedure.

(For a complete example that does not rely on pseudo-code, see Session Variables and Caller’s Rights vs. Owner’s Rights Stored Procedures.)

Often, this behavior is desirable. For example, in many stored procedures, you’ll want to inherit context information such as the current database and the current session-level variables.

However, in some cases, you might want your stored procedure to be more isolated. For example, if your stored procedure sets a session-level variable, you might not want the session-level variable to influence future statements outside your stored procedure.

To better isolate your stored procedure from the rest of your session:

  • Avoid using session-level variables directly. Instead, pass them as explicit parameters. This forces the caller to think about exactly what values the stored procedure will use.
  • Clean up any session-level variables that you set inside the stored procedure (and use names that are very unlikely to be used anywhere else).

For example:

CREATE PROCEDURE sv_proc1()
  returns varchar language javascript
  as
  $$
  rs = snowflake.execute( {sqlText: "SET SESSION_VAR_ZYXW = 51"} );

  rs = snowflake.execute( {sqlText: "SELECT 2 * $SESSION_VAR_ZYXW"} );
  rs.next();
  var MyString = rs.getColumnValue(1);

  rs = snowflake.execute( {sqlText: "UNSET SESSION_VAR_ZYXW"} );

  return MyString;
  $$
  ;
CALL sv_proc1();
-- This fails because SESSION_VAR_ZYXW is no longer defined.
SELECT $SESSION_VAR_ZYXW;

SET Variable1 = 49;
CREATE PROCEDURE sv_proc2(parameter1 FLOAT)
  returns varchar
  language javascript
  as
  $$
  rs = snowflake.execute( {sqlText: "SELECT 2 * " + PARAMETER1} );
  rs.next();
  var MyString = rs.getColumnValue(1);
  return MyString;
  $$
  ;
CALL sv_proc2($Variable1);

If you are familiar with programming in languages such as C, Java, etc., it is important to understand that variables you set inside a stored procedure are not like the local variables in C that disappear when a C function finishes running. Isolating your stored procedure from its environment takes more effort in SQL than in C.

Owner’s Rights Stored Procedures

Owner’s rights stored procedures:

  • Run with the privileges of the owner, not the privileges of the caller.
  • Inherit the current warehouse of the caller.
  • By default, use the database and schema that the stored procedure is stored in.
  • Cannot read, SET, UNSET, the caller’s session variables.
  • Can read only whitelisted session parameters, and cannot SET or UNSET any of the caller’s session parameters.

General Tips

These tips apply broadly to both owner’s rights and caller’s rights stored procedures.

Tip

If you are familiar with programming in assembly language, you might find the following analogy helpful. In assembly language, functions often create and undo their environments in a symmetric way. For example:

-- Set up.
push a;
push b;
...
-- Clean up in the reverse order that we set up.
pop b;
pop a;

You might want to use this approach in some of your stored procedures. If a stored procedure makes temporary changes to your session, then it should undo those changes before it returns. Additionally, if a stored procedure has exception handling or branching, or other logic that might impact which statements are affected, you need to clean up whatever you created, regardless of which branches you take during a particular invocation.

For example:

-- This is pseudo-code.
create procedure f() ...
    $$
    set x;
    set y;
    try  {
       set z;
       -- Do something interesting...
       ...
       unset z;
       }
    catch  {
       -- Give error message...
       ...
       unset z;
       }
    unset y;
    unset x;
    $$
    ;

Tip

SQL statements can be quite long, and it is not always practical to fit them on a single line. JavaScript treats a newline as the end of a statement. If you want to split a long SQL statement across multiple lines, you can use the usual JavaScript techniques for handling long strings, including:

  • Put a backslash (line continuation character) immediately prior to the end of the line. For example:

    var sql_command = "SELECT * \
                           FROM table1;";
    
  • Use backticks (single backquotes) rather than double quotes around the string, for example:

    var sql_command = `SELECT *
                           FROM table1;`;
    
  • Accumulate the string, for example:

    var sql_command = "SELECT col1, col2"
    sql_command += "     FROM table1"
    sql_command += "     WHERE col1 >= 100"
    sql_command += "     ORDER BY col2;"
    

Naming Conventions for Stored Procedures

Stored procedures are database objects, meaning that they are created in a specified database and schema. As such, they have a fully-qualified name defined by their namespace, in the form of db.schema.procedure_name. When called without their fully-qualified name, stored procedures are resolved according to the database and schema in use for the session.

Overloading of Names

Snowflake supports overloading of stored procedure names. In other words, multiple stored procedures in the same schema can have the same name, as long as their argument signatures differ, either by the number of arguments or the argument types. When an overloaded stored procedure is called, the server checks the arguments and calls the correct stored procedure.

An example is included at Overloading Stored Procedure Names.

Potential Conflicts with System-defined Functions and User-defined Functions

Stored procedures and user-defined functions can have the same names if they have different numbers or data types of arguments.

However, Snowflake does not allow creating stored procedures with the same name as any of the system-defined functions.

Transaction Management

Transaction control commands (BEGIN, COMMIT, ROLLBACK) are not allowed inside a stored procedure.

A stored procedure can be called:

  • Entirely inside a transaction, for example:

    BEGIN;
    W;
    CALL MyProcedure();   -- executes X and Y
    Z;
    COMMIT;
    
  • Outside a transaction (i.e. not after an unclosed BEGIN).

In either case, BEGIN, COMMIT, and ROLLBACK are not allowed inside the stored procedure.

Remember that DDL statements (CREATE TABLE, etc.) cause an implicit COMMIT. Such statements are not allowed inside a stored procedure if that procedure is called inside a transaction. For example, do not do the following:

CREATE PROCEDURE do_not_call_inside_transaction...
AS
$$
    ...
    stmt = snowflake.createStatement(
        {sqlText: "CREATE TABLE not_a_good_idea_here ..."}
        );
    rs = stmt.execute();
    ...
$$;

BEGIN;
...
CALL do_not_call_inside_transaction();   -- Tries to do an implicit commit due to DDL.
...
COMMIT;

Calling a Stored Procedure

To execute a stored procedure, use the CALL statement, for example:

call stproc1(5.14::FLOAT);

Each argument to a stored procedure can be a general expression, for example:

CALL stproc1(2 * 5.14::FLOAT);

An argument can even be a subquery, for example:

CALL stproc1(SELECT COUNT(*) FROM stproc_test_table1);

You can call only one stored procedure per CALL statement. The following fails:

call proc1(1), proc2(2);                          -- Not allowed

Also, you cannot use a stored procedure call as part of an expression. For example, the following fail:

call proc1(1) + proc1(2);                         -- Not allowed
call proc1(1) + 1;                                -- Not allowed
call proc1(proc2(x));                             -- Not allowed
select * from (call proc1(1));                    -- Not allowed

However, inside a stored procedure, the stored procedure can call another stored procedure, or call itself recursively. An example of this is shown in the code examples section.

Caution

Nested calls can exceed the maximum allowed stack depth, so be careful when doing this, especially when using recursion.

Access Control Privileges

There are two types of privileges that are related to stored procedures:

  • Privileges directly on the stored procedure itself.
  • Privileges on the database objects (e.g. tables) that the stored procedure accesses.

Privileges on Stored Procedures

Similar to other database objects (tables, views, UDFs, etc.), stored procedures are owned by a role and have one or more privileges that can be granted to other roles.

Currently, the following privileges apply to stored procedures:

  • USAGE.
  • OWNERSHIP.

For a role to use a stored procedure, the role must either be the owner or have been granted USAGE privilege on the stored procedure.

Privileges on the Database Objects Accessed by the Stored Procedure

When the stored procedure runs, it can run with the privileges of the owner of the stored procedure or the privileges of the caller of the stored procedure.

The creator of the procedure specifies whether the procedure is an owner’s rights procedure or a caller’s rights procedure at the time that the procedure is created. (The owner can also change from owner’s rights to caller’s rights or vice-versa by executing an ALTER PROCEDURE command.)

By default, a stored procedure is created with owner’s rights.

Caller’s Rights Stored Procedures

When a stored procedure runs with the caller’s rights, the procedure executes with the privileges of the caller.

For example, if the stored procedure tries to read from a table that the caller does not have privileges to read, then the statement that attempts to read from the table fails.

If a procedure is a caller’s rights procedure, the caller does not have have the privilege to view the code in the stored procedure (unless the caller is also the owner). In other words, having USAGE privilege is not sufficient to allow you to view the code in the stored procedure.

Owner’s Rights Stored Procedures

When a stored procedure runs with the owner’s rights, the procedure executes (mostly) according to the privileges of the owner. This enables the owner of the stored procedure to allow other roles to perform controlled operations on database objects that they do not own or have privileges to access directly.

For example, suppose that user Jane creates a table and does not grant user David any privileges on that table. Suppose also that Jane creates a stored procedure that deletes data more than one year old from that table. If Jane creates the stored procedure as an owner’s rights procedure, and Jane as the table’s owner has the right to delete rows from that table, and Jane grants David privileges to call that procedure, then user David can run that stored procedure and it will delete the rows, just as if Jane herself had run the DELETE command.

Effectively, the creator can delegate the authority to perform the specific actions taken in the stored procedure without granting broader privileges on the underlying database objects.

Owner’s rights stored procedures have several restrictions. These restrictions affect the following:

  • Access to SQL variables.
  • Access to session/account parameters.
  • Use of Temporary tables.
  • Ability to call GET_DDL to get the stored procedure’s DDL.
  • Ability to call the function CURRENT_USER.
  • Ability to execute ALTER USER statements.
  • Monitoring stored procedures at execution time

The following sections explain these restrictions in more detail.

SQL Variables:

A stored procedure does not have access to SQL Variables created outside the stored procedure. This restriction prevents a stored procedure written or owned by one user from reading SQL variables created by another user (the stored procedure caller).

If your stored procedure needs values that are stored in SQL variables, then the values in those variables should be passed as explicit arguments to the stored procedure, for example:

SET PROVINCE = 'Manitoba';
CALL MyProcedure($PROVINCE);
Session Parameters:
 

If one user wrote a stored procedure, and another user called that stored procedure, and if the stored procedure were allowed to read all session parameters, then it would be possible for the writer (owner) of the stored procedure to read session parameters set by the caller without the caller necessarily knowing it. (For more information about session parameters, see Parameters, ALTER SESSION and SHOW PARAMETERS.)

In some cases, allowing the stored procedure to read the caller’s session parameters could be helpful; the stored procedure could use information specific to customize the behavior for that caller or session. For example, the stored procedure could use the caller’s preferred DATE_OUTPUT_FORMAT.

However, in other cases, reading the caller’s session parameters might not be desirable.

  • If the author (owner) of a stored procedure has set a specific session parameter, but users (callers) of the stored procedure have not set that parameter, then the stored procedure might fail or behave differently when called by users other than the author.
  • Callers might not want stored procedures to read their session parameters if those parameters contain confidential information.

To reduce these potential issues, Snowflake allows a stored procedure to use only a specific subset of the caller’s session parameters. (These whitelisted parameters are listed below.)

If a statement inside a stored procedure references a non-whitelisted parameter, then the stored procedure uses the value of the owner’s account-level parameter, not the caller’s session level parameter.

If the stored procedure’s owner never set the account parameter explicitly, then the stored procedure uses the default value for that account parameter. (All account-level parameters have default values.)

The list of whitelisted session parameters might change over time. The current list is shown below:

  • AUTOCOMMIT
  • BINARY_INPUT_FORMAT
  • BINARY_OUTPUT_FORMAT
  • DATE_INPUT_FORMAT
  • DATE_OUTPUT_FORMAT
  • ENABLE_UNLOAD_PHYSICAL_TYPE_OPTIMIZATION
  • ERROR_ON_NONDETERMINISTIC_MERGE
  • ERROR_ON_NONDETERMINISTIC_UPDATE
  • JDBC_TREAT_DECIMAL_AS_INT
  • JSON_INDENT
  • LOCK_TIMEOUT
  • MAX_CONCURRENCY_LEVEL
  • ODBC_USE_CUSTOM_SQL_DATA_TYPES
  • PERIODIC_DATA_REKEYING
  • QUERY_TAG
  • QUERY_WAREHOUSE_NAME
  • ROWS_PER_RESULTSET
  • STATEMENT_QUEUED_TIMEOUT_IN_SECONDS
  • STATEMENT_TIMEOUT_IN_SECONDS
  • STRICT_JSON_OUTPUT
  • TIMESTAMP_DAY_IS_ALWAYS_24H
  • TIMESTAMP_INPUT_FORMAT
  • TIMESTAMP_LTZ_OUTPUT_FORMAT
  • TIMESTAMP_NTZ_OUTPUT_FORMAT
  • TIMESTAMP_OUTPUT_FORMAT
  • TIMESTAMP_TYPE_MAPPING
  • TIMESTAMP_TZ_OUTPUT_FORMAT
  • TIMEZONE
  • TIME_INPUT_FORMAT
  • TIME_OUTPUT_FORMAT
  • TRANSACTION_ABORT_ON_ERROR
  • TRANSACTION_DEFAULT_ISOLATION_LEVEL
  • TWO_DIGIT_CENTURY_START
  • UI_MODE
  • UNSUPPORTED_DDL_ACTION
  • USE_CACHED_RESULT
  • WEEK_OF_YEAR_POLICY
  • WEEK_START
Stored Procedures and Temporary Tables:
 

Stored procedures are not allowed to create TEMP tables, i.e. tables that are automatically dropped at the end of the session.

(The reason for this restriction is that the length of time that the temp table exists should be determined by the duration of the caller’s session, but information about temp tables is stored in the stored procedure owner’s schema, which is not read directly by the caller’s session cleanup code.)

GET_DDL and stored procedures:
 

If a stored procedure is created as an owner’s rights stored procedure, then callers (other than the owner) cannot view the body of the stored procedure by calling GET_DDL( <procedure_name>, ... ).

ALTER USER:

The following restrictions apply to ALTER USER statements in an owner’s right stored procedure:

  • Owner’s Rights stored procedures cannot execute ALTER USER statements that implicitly use the current user for the session.
  • Owner’s Rights stored procedures can execute ALTER USER statements that explicitly identify the user, as long as the user is not the current user.
Monitoring stored procedures at execution time:
 

Neither the owner nor the caller of an owner’s rights stored procedure necessarily has privileges to monitor execution of the stored procedure.

A user with the WAREHOUSE MONITOR privilege can monitor execution of the individual warehouse-related SQL statements within that stored procedure. Most queries and DML statements are warehouse-related statements. DDL statements, such as CREATE, ALTER, etc. do not use the warehouse and cannot be monitored as part of monitoring stored procedures.

Most restrictions on an owner’s rights stored procedure apply to all callers, including the owner.

Nested Stored Procedures

If an owner’s rights stored procedure is called by a caller’s rights stored procedure, or vice-versa, which rules apply?

  • An owner’s rights stored procedure always behaves as an owner’s rights stored procedure, no matter where it was called from.
  • A stored procedure behaves as a caller’s rights stored procedure if the procedure itself and the entire call hierarchy above it are caller’s rights stored procedures. If an owner’s rights stored procedure is anywhere above it in the call chain, then it behaves as an owner’s rights stored procedure.

Choosing Between Owner’s Rights and Caller’s Rights

Create a stored procedure as an owner’s rights stored procedure if all of the following are true:

  • You want to delegate a task(s) to another user(s) who will run with the owner’s privileges, not the caller’s own privileges. For example, if you want a user without DELETE privilege on a table to be able to call a stored procedure that deletes old data, but not current data, then you probably want to use an owner’s rights stored procedure.
  • The restrictions in Owner’s Rights Stored Procedures will not prevent the stored procedure from working properly.

Create a stored procedure as a caller’s rights stored procedure if the following are true:

  • The stored procedure operates only on objects that the caller owns or has the required privileges on.
  • The restrictions in Owner’s Rights Stored Procedures would prevent the stored procedure from working. For example, use a caller’s rights procedure if the caller of the stored procedure needs to use that caller’s environment, for example session variables or account parameters.

Stored Procedure Considerations

JavaScript Error Handling

Because a stored procedure is written in JavaScript, it can use JavaScript’s try/catch syntax.

Restrictions on Stored Procedures

Restrictions on Functions and Commands within a Stored Procedure

The following commands/functions are not allowed within a stored procedure:

  • Currently, transaction control commands (BEGIN, COMMIT, ROLLBACK) are not allowed inside a stored procedure. This means that a stored procedure is executed entirely within a single transaction (explicitly or implicitly).

Other Restrictions

  • The JavaScript commands in a stored procedure cannot import 3rd-party libraries. Allowing 3rd-party libraries could create security holes.
  • Although stored procedures allow nesting and recursion, the current maximum stack depth is approximately 8, and can be less if individual stored procedures in the call chain consume large amounts of resources.

Case-sensitivity in JavaScript Arguments

Argument names are case-insensitive in the SQL portion of the stored procedure code, but are case-sensitive in the JavaScript portion.

For stored procedures (and UDFs) that use JavaScript, identifiers (such as argument names) in the SQL portion of the statement are converted to uppercase automatically (unless you delimit the identifier with double quotes), while argument names in the JavaScript portion will be left in their original case. This can cause your stored procedure to fail without returning an explicit error message because the arguments aren’t seen.

Here is an example of a stored procedure in which the name of an argument in the JavaScript code does not match the name of the argument in the SQL code merely because the case will be different:

CREATE PROCEDURE f(argument1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
$$
var local_variable1 = argument1;  // Wrong
var local_variable2 = ARGUMENT1;  // Right
$$;

Using uppercase identifiers (especially argument names) consistently across your SQL statements and JavaScript code tends to reduce silent errors.

JavaScript Delimiters

The JavaScript portion of the stored procedure code must be enclosed within a pair of identical delimiters. These delimiters can be almost any sequence of non-whitespace, printable ASCII characters, such as $$ or '. By convention, we use single quotes (') in the syntax diagrams and double dollar signs ($$) in code samples.

As a practical matter, using a common single character such as the single quote character (') as the delimiter makes it more difficult to use the same character inside the stored procedure (i.e. you’ll need to “escape” it).

For example, if you use ' to delimit your stored procedure, be careful about using the same character in the body of the stored procedure to ensure that the embedded character is not interpreted as the end of the procedure. For simplicity, we recommend that you use a multi-character delimiter that is rarely, if ever, found in the body of a stored procedure (e.g. $$).

Code Requirements

The JavaScript code must define a single literal JavaScript object for the stored procedure to be valid.

If the JavaScript code does not meet this requirement, the stored procedure will still be created; however, it will fail when called.

Runtime Errors

Most errors in stored procedures show up at runtime because the JavaScript code is interpreted at the time that the stored procedure runs rather than when the stored procedure is created.

Support for Dynamic SQL

Stored procedures can be used to dynamically construct SQL statements. For example, you could build a SQL command string that contains a mix of pre-configured SQL and user inputs (e.g. a user’s account number). For examples, see Dynamically Creating SQL Statements and the Examples section.

Synchronous API

The API for Snowflake stored procedures is synchronous. Within a stored procedure, you can run only one one thread at a time.

Note that this is different from the rule for the JavaScript executing with the Node.js connector, which allows you to run asynchronous threads.

SQL Injection

Stored procedures allow you to dynamically create a SQL statement and execute it. However, this can open up the possibility of SQL injection attacks, particularly if you create the SQL statement using input from a public or untrusted source.

You can minimize the danger of SQL injection attacks by binding parameters rather than concatenating text. For examples of binding data in JavaScript, see Binding Statement Parameters.

If you choose to use concatenation, you should check inputs carefully when constructing SQL dynamically using input from public sources. You might also want to take other precautions, such as querying using a role that has limited privileges (i.e. read-only access, or access to only certain tables or views).

For more information about SQL Injection attacks, see SQL injection (in Wikipedia).

Examples

Basic Examples

  • The following example shows the basic syntax of creating and calling a stored procedure. It doesn’t execute any SQL or procedural code. However, it provides a starting point for more realistic examples later:

    The “$$” delimiter marks the beginning and end of the JavaScript code.

    create or replace procedure sp_pi()
        returns float not null
        language javascript
        as
        $$
        return 3.1415926;
        $$
        ;
    
    CALL sp_pi();
    +-----------+
    |     SP_PI |
    |-----------|
    | 3.1415926 |
    +-----------+
    
  • The following example illustrates how to execute a SQL statement inside a stored procedure:

    1. Create a table:

      CREATE TABLE stproc_test_table1 (num_col1 numeric(14,7));
      
    2. Create a stored procedure. This merely inserts a row into an existing table named stproc_test_table1 and returns the value “Succeeded.”. The returned value is not very useful from a SQL perspective, but it allows you to return status information, for example “Succeeded.” or “Failed.” to the user.

      create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)
          returns string
          language javascript
          strict
          execute as owner
          as
          $$
          var sql_command = 
           "INSERT INTO stproc_test_table1 (num_col1) VALUES (" + FLOAT_PARAM1 + ")";
          try {
              snowflake.execute (
                  {sqlText: sql_command}
                  );
              return "Succeeded.";   // Return a success/error indicator.
              }
          catch (err)  {
              return "Failed: " + err;   // Return a success/error indicator.
              }
          $$
          ;
      
    3. Call the stored procedure:

      call stproc1(5.14::FLOAT);
      +------------+
      | STPROC1    |
      |------------|
      | Succeeded. |
      +------------+
      
    4. Confirm that the stored procedure inserted the row:

      -- Confirm that the stored procedure inserted the new row.
      select * from stproc_test_table1;
      +-----------+
      |  NUM_COL1 |
      |-----------|
      | 5.1400000 |
      +-----------+
      
  • The following example retrieves a result:

    1. Create a procedure to count the number of rows in a table (this is equivalent to SELECT COUNT(*) FROM <table>).

      create or replace procedure get_row_count(table_name VARCHAR)
        returns float not null
        language javascript
        as
        $$
        var row_count = 0;
        // Dynamically compose the SQL statement to execute.
        var sql_command = "select count(*) from " + TABLE_NAME;
        // Run the statement.
        var stmt = snowflake.createStatement(
               {
               sqlText: sql_command
               }
            );
        var res = stmt.execute();
        // Get back the row count. Specifically, ...
        // ... get the first (and in this case only) row from the result set ...
        res.next();
        // ... and then get the returned value, which in this case is the number of
        // rows in the table.
        row_count = res.getColumnValue(1);
        return row_count;
        $$
        ;
      
    2. Ask the stored procedure how many rows are in the table:

      call get_row_count('stproc_test_table1');
      +---------------+
      | GET_ROW_COUNT |
      |---------------|
      |             3 |
      +---------------+
      
    3. Check independently that you got the right number:

      select count(*) from stproc_test_table1;
      +----------+
      | COUNT(*) |
      |----------|
      |        3 |
      +----------+
      

Recursive Stored Procedure Example

This shows an example of an artificially simple recursive stored procedure.

create or replace table stproc_test_table2 (col1 FLOAT);
create or replace procedure recursive_stproc(counter FLOAT)
    returns varchar not null
    language javascript
    as
    -- "$$" is the delimiter that shows the beginning and end of the stored proc.
    $$
    var counter1 = COUNTER;
    var returned_value = "";
    var accumulator = "";
    var stmt = snowflake.createStatement(
        {
        sqlText: "INSERT INTO stproc_test_table2 (col1) VALUES (?);",
        binds:[counter1]
        }
        );
    var res = stmt.execute();
    if (COUNTER > 0)
        {
        stmt = snowflake.createStatement(
            {
            sqlText: "call recursive_stproc (?);",
            binds:[counter1 - 1]
            }
            );
        res = stmt.execute();
        res.next();
        returned_value = res.getColumnValue(1);
        }
    accumulator = accumulator + counter1 + ":" + returned_value;
    return accumulator;
    $$
    ;
call recursive_stproc(4.0::FLOAT);
+------------------+
| RECURSIVE_STPROC |
|------------------|
| 4:3:2:1:0:       |
+------------------+
SELECT * 
    FROM stproc_test_table2
    ORDER BY col1;
+------+
| COL1 |
|------|
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+

Dynamically Creating SQL Statements

The following example shows how to dynamically create a SQL statement. (Again, be careful to prevent SQL Injection attacks when using dynamic SQL.)

Create the stored procedure. This procedure allows you to pass the name of a table and get the number of rows in that table. This is equivalent to select count(*) from table_name.

create or replace procedure get_row_count(table_name VARCHAR)
    returns float 
    not null
    language javascript
    as
    $$
    var row_count = 0;
    // Dynamically compose the SQL statement to execute.
    // Note that we uppercased the input parameter name.
    var sql_command = "select count(*) from " + TABLE_NAME;
    // Run the statement.
    var stmt = snowflake.createStatement(
           {
           sqlText: sql_command
           }
        );
    var res = stmt.execute();
    // Get back the row count. Specifically, ...
    // ... first, get the first (and in this case only) row from the
    //  result set ...
    res.next();
    // ... then extract the returned value (which in this case is the
    // number of rows in the table).
    row_count = res.getColumnValue(1);
    return row_count;
    $$
    ;

Call the stored procedure.

call get_row_count('stproc_test_table1');
+---------------+
| GET_ROW_COUNT |
|---------------|
|             3 |
+---------------+

Show the results from SELECT COUNT(*) for the same table:

SELECT COUNT(*) FROM stproc_test_table1;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+

Retrieving Result Set Metadata

This shows an example of retrieving a small amount of metadata from a result set:

create or replace table stproc_test_table3 (
    n10 numeric(10,0),     /* precision = 10, scale = 0 */
    n12 numeric(12,4),     /* precision = 12, scale = 4 */
    v1 varchar(19)         /* scale = 0 */
    );
create or replace procedure get_column_scale(column_index float)
    returns float not null
    language javascript
    as
    $$
    var stmt = snowflake.createStatement(
        {sqlText: "select n10, n12, v1 from stproc_test_table3;"}
        );
    stmt.execute();  // ignore the result set; we just want the scale.
    return stmt.getColumnScale(COLUMN_INDEX); // Get by column index (1-based)
    $$
    ;
call get_column_scale(1);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+
call get_column_scale(2);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                4 |
+------------------+
call get_column_scale(3);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+

Catching an Error using Try/Catch

This shows an example of using a JavaScript try/catch block to catch an error inside a stored procedure:

Create the stored procedure:

    create procedure broken()
      returns varchar not null
      language javascript
      as
      $$
      var result = "";
      try {
          snowflake.execute( {sqlText: "Invalid Command!;"} );
          result = "Succeeded";
          }
      catch (err)  {
          result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
          result += "\n  Message: " + err.message;
          result += "\nStack Trace:\n" + err.stackTraceTxt; 
          }
      return result;
      $$
      ;

Call the stored procedure. This should return an error showing the error number and other information:

    -- This is expected to fail.
    call broken();
+---------------------------------------------------------+
| BROKEN                                                  |
|---------------------------------------------------------|
| Failed: Code: 100183                                    |
|   State: P0000                                          |
|   Message: SQL compilation error:                       |
| syntax error line 1 at position 0 unexpected 'Invalid'. |
| Stack Trace:                                            |
| At Snowflake.execute, line 4 position 20                |
+---------------------------------------------------------+

Overloading Stored Procedure Names

As discussed in the section Naming Conventions for Stored Procedures, you can overload stored procedure names. Below are some examples.

First, create two stored procedures that have the same names, but different numbers of arguments.

create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)
    returns string
    language javascript
    strict
    as
    $$
    return FLOAT_PARAM1;
    $$
    ;
create or replace procedure stproc1(FLOAT_PARAM1 FLOAT, FLOAT_PARAM2 FLOAT)
    returns string
    language javascript
    strict
    as
    $$
    return FLOAT_PARAM1 * FLOAT_PARAM2;
    $$
    ;

Here are examples of calling these two procedures:

call stproc1(5.14::FLOAT);
+---------+
| STPROC1 |
|---------|
| 5.14    |
+---------+
call stproc1(5.14::FLOAT, 2.00::FLOAT);
+---------+
| STPROC1 |
|---------|
| 10.28   |
+---------+

Using RESULT_SCAN to Retrieve the Result from a Stored Procedure

This example shows how to use the RESULT_SCAN() function to retrieve the result from a CALL.

Create the table:

CREATE TABLE western_provinces(ID INT, province VARCHAR);

Create the stored procedure. This procedure returns a well-formatted string that looks like a result set of three rows, but is actually a single string.

CREATE OR REPLACE PROCEDURE read_western_provinces()
  RETURNS VARCHAR NOT NULL
  LANGUAGE JAVASCRIPT
  AS
  $$
  var return_value = "";
  try {
      command = "SELECT * FROM western_provinces ORDER BY province;"
      stmt = snowflake.createStatement( {sqlText: command } );
      rs = stmt.execute();
      if (rs.next())  {
          return_value += rs.getColumnValue(1);
          return_value += ", " + rs.getColumnValue(2);
          }
      while (rs.next())  {
          return_value += "\n";
          return_value += rs.getColumnValue(1);
          return_value += ", " + rs.getColumnValue(2);
          }
      }
  catch (err)  {
      result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
      result += "\n  Message: " + err.message;
      result += "\nStack Trace:\n" + err.stackTraceTxt;
      }
  return return_value;
  $$
  ;

Call the stored procedure, then retrieve the results by using RESULT_SCAN:

CALL read_western_provinces();
+------------------------+
| READ_WESTERN_PROVINCES |
|------------------------|
| 1, Alberta             |
| 2, British Columbia    |
| 3, Manitoba            |
+------------------------+
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+------------------------+
| READ_WESTERN_PROVINCES |
|------------------------|
| 1, Alberta             |
| 2, British Columbia    |
| 3, Manitoba            |
+------------------------+

You can do more complex operations on the value returned by the RESULT_SCAN function. In this case, since the returned value is a single string, you might want to extract the individual “rows” that appear to be contained within that string, and store those rows in another table. The following example (which is a continuation of the example above) shows one way to do that.

Create a table for long-term storage. This table contains the province name and the province ID after we’ve extracted them from the string returned by the CALL command.

CREATE TABLE all_provinces(ID INT, province VARCHAR);

Call the stored procedure, then retrieve the result by using RESULT_SCAN, and then extract the three rows from the string and put those rows into the table:

INSERT INTO all_provinces
  WITH 
    one_string (string_col) AS
      (SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))),
    three_strings (one_row) AS
      (SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n'))
  SELECT
         STRTOK(one_row, ',', 1) AS ID,
         STRTOK(one_row, ',', 2) AS province
    FROM three_strings
    WHERE NOT (ID IS NULL AND province IS NULL);
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+

Double-check that this worked by showing the rows in the table:

SELECT ID, province 
    FROM all_provinces;
+----+-------------------+
| ID | PROVINCE          |
|----+-------------------|
|  1 |  Alberta          |
|  2 |  British Columbia |
|  3 |  Manitoba         |
+----+-------------------+

Here’s approximately the same code, but in smaller steps:

Create a table named one_string. We’ll use this to temporarily store the result of the CALL command. Since the result of the CALL is a single string, this table stores only a single VARCHAR.

CREATE TRANSIENT TABLE one_string(string_col VARCHAR);

Call the stored procedure, then retrieve the result (a string) by using RESULT_SCAN, and then store that into the intermediate table named one_string:

CALL read_western_provinces();
+------------------------+
| READ_WESTERN_PROVINCES |
|------------------------|
| 1, Alberta             |
| 2, British Columbia    |
| 3, Manitoba            |
+------------------------+
INSERT INTO one_string
    SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+

This shows the new row in the one_string table. Remember that although this is formatted to look like three rows, it’s really just one string:

SELECT string_col FROM one_string;
+---------------------+
| STRING_COL          |
|---------------------|
| 1, Alberta          |
| 2, British Columbia |
| 3, Manitoba         |
+---------------------+
-- Show that it's one string, not three rows:
SELECT '>>>' || string_col || '<<<' AS string_col 
    FROM one_string;
+---------------------+
| STRING_COL          |
|---------------------|
| >>>1, Alberta       |
| 2, British Columbia |
| 3, Manitoba<<<      |
+---------------------+
SELECT COUNT(*) FROM one_string;
+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

The following commands show how we can extract multiple rows from that one string:

SELECT * FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
+---------------------+-----+-------+---------------------+
| STRING_COL          | SEQ | INDEX | VALUE               |
|---------------------+-----+-------+---------------------|
| 1, Alberta          |   1 |     1 | 1, Alberta          |
| 2, British Columbia |     |       |                     |
| 3, Manitoba         |     |       |                     |
| 1, Alberta          |   1 |     2 | 2, British Columbia |
| 2, British Columbia |     |       |                     |
| 3, Manitoba         |     |       |                     |
| 1, Alberta          |   1 |     3 | 3, Manitoba         |
| 2, British Columbia |     |       |                     |
| 3, Manitoba         |     |       |                     |
+---------------------+-----+-------+---------------------+
SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
+---------------------+
| VALUE               |
|---------------------|
| 1, Alberta          |
| 2, British Columbia |
| 3, Manitoba         |
+---------------------+

Create a table named three_strings. This table will hold the result after we’ve split it into individual lines/strings.

CREATE TRANSIENT TABLE three_strings(string_col VARCHAR);

Now convert that one string in the one_string table into three separate strings, and prove that it’s really 3 strings this time:

INSERT INTO three_strings
  SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
SELECT string_col 
    FROM three_strings;
+---------------------+
| STRING_COL          |
|---------------------|
| 1, Alberta          |
| 2, British Columbia |
| 3, Manitoba         |
+---------------------+
SELECT COUNT(*) 
    FROM three_strings;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+

Now convert those three strings into three rows in our long-term table named all_provinces:

INSERT INTO all_provinces
  SELECT 
         STRTOK(string_col, ',', 1) AS ID, 
         STRTOK(string_col, ',', 2) AS province 
    FROM three_strings
    WHERE NOT (ID IS NULL AND province IS NULL);
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+

Show the three rows in the long-term table:

SELECT ID, province 
    FROM all_provinces;
+----+-------------------+
| ID | PROVINCE          |
|----+-------------------|
|  1 |  Alberta          |
|  2 |  British Columbia |
|  3 |  Manitoba         |
+----+-------------------+
SELECT COUNT(*) 
    FROM all_provinces;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+

Returning an Array of Error Messages

Your stored procedure might execute more than one SQL statement. You might want to return a status/error message for each SQL statement. Unfortunately, a stored procedure returns a single row. It is not designed to return multiple rows, e.g. one row/message for each SQL statement executed in the stored procedure. However, if all of your messages fit into a single value of type ARRAY, you can get all the messages from a stored procedure with some extra effort. The example below shows one way to do this. (The error messages are faked, but you can extend this code to work with your actual SQL statements.)

CREATE OR REPLACE PROCEDURE sp_return_array()
      RETURNS VARIANT NOT NULL
      LANGUAGE JAVASCRIPT
      AS
      $$
      // This array will contain one error message (or an empty string) 
      // for each SQL command that we executed.
      var array_of_rows = [];

      // Artificially fake the error messages.
      array_of_rows.push("ERROR: The foo was barred.")
      array_of_rows.push("WARNING: A Carrington Event is predicted.")

      return array_of_rows;
      $$
      ;
CALL sp_return_array();
+-----------------------------------------------+
| SP_RETURN_ARRAY                               |
|-----------------------------------------------|
| [                                             |
|   "ERROR: The foo was barred.",               |
|   "WARNING: A Carrington Event is predicted." |
| ]                                             |
+-----------------------------------------------+
-- Now get the individual error messages, in order.
SELECT INDEX, VALUE 
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS res, LATERAL FLATTEN(INPUT => res.$1)
    ORDER BY index
    ;
+-------+---------------------------------------------+
| INDEX | VALUE                                       |
|-------+---------------------------------------------|
|     0 | "ERROR: The foo was barred."                |
|     1 | "WARNING: A Carrington Event is predicted." |
+-------+---------------------------------------------+

Remember, this is not a general purpose solution. There is a limit on the maximum size of ARRAY data types, and your entire result set must fit into a single ARRAY.

Returning a Result Set

This section extends the previous example (“Returning an Array of Error Messages”). This example is more general, and allows you to return a result set from a query.

A stored procedure returns a single row that contains a single column. It is not designed to return a result set. However, if your result set is small enough to fit into a single value of type VARIANT or ARRAY, you can get a result set from a stored procedure with some extra effort.

CREATE TABLE return_to_me(col_i INT, col_v VARCHAR);
INSERT INTO return_to_me (col_i, col_v) VALUES
    (1, 'Ariel'),
    (2, 'October'),
    (3, NULL),
    (NULL, 'Project');
-- Create the stored procedure that retrieves a result set and returns it.
CREATE OR REPLACE PROCEDURE sp_return_table(TABLE_NAME VARCHAR, COL_NAMES ARRAY)
      RETURNS VARIANT NOT NULL
      LANGUAGE JAVASCRIPT
      AS
      $$
      // This variable will hold a JSON data structure that holds ONE row.
      var row_as_json = {};
      // This array will contain all the rows.
      var array_of_rows = [];
      // This variable will hold a JSON data structure that we can return as
      // a VARIANT.
      // This will contain ALL the rows in a single "value".
      var table_as_json = {};

      // Run SQL statement(s) and get a resultSet.
      var command = "SELECT * FROM " + TABLE_NAME;
      cmd1_dict = {sqlText: command};
      stmt = snowflake.createStatement(cmd1_dict);
      rs = stmt.execute();

      // Read each row and add it to the array we will return.
      var row_num = 1;
      while (rs.next())  {
        // Put each row in a variable of type JSON.
        row_as_json = {};
        // For each column in the row...
        for (col_num = 0; col_num < COL_NAMES.length; col_num = col_num + 1) {
          col_name = COL_NAMES[col_num];
          row_as_json[col_name] = rs.getColumnValue(col_num + 1);
          }
        // Add the row to the array of rows.
        array_of_rows.push(row_as_json);
        ++row_num;
        }
      // Put the array in a JSON variable (so it looks like a VARIANT to
      // Snowflake).  The key is "key1", and the value is the array that has
      // the rows we want.
      table_as_json = { "key1" : array_of_rows };

      // Return the rows to Snowflake, which expects a JSON-compatible VARIANT.
      return table_as_json;
      $$
      ;
CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
-- Use "ResultScan" to get the data from the stored procedure that
-- "did not return a result set".
-- Use "$1:key1" to get the value corresponding to the JSON key named "key1".
SELECT $1:key1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+------------------------+
| $1:KEY1                |
|------------------------|
| [                      |
|   {                    |
|     "COL_I": 1,        |
|     "COL_V": "Ariel"   |
|   },                   |
|   {                    |
|     "COL_I": 2,        |
|     "COL_V": "October" |
|   },                   |
|   {                    |
|     "COL_I": 3,        |
|     "COL_V": null      |
|   },                   |
|   {                    |
|     "COL_I": null,     |
|     "COL_V": "Project" |
|   }                    |
| ]                      |
+------------------------+
-- Now get what we really want.
SELECT VALUE:COL_I AS col_i, value:COL_V AS col_v
       FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS res, LATERAL FLATTEN(input => res.$1)
  ORDER BY COL_I;
+-------+-----------+
| COL_I | COL_V     |
|-------+-----------|
| 1     | "Ariel"   |
| 2     | "October" |
| 3     | null      |
| null  | "Project" |
+-------+-----------+

This shows how to combine the previous two lines into one (difficult-to-understand) line:

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT VALUE:COL_I AS col_i, value:COL_V AS col_v
       FROM (SELECT $1:key1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))) AS res,
            LATERAL FLATTEN(input => res.$1)
       ORDER BY COL_I;
+-------+-----------+
| COL_I | COL_V     |
|-------+-----------|
| 1     | "Ariel"   |
| 2     | "October" |
| 3     | null      |
| null  | "Project" |
+-------+-----------+

For convenience, you can wrap the preceding line in a view. (This view also converts the string ‘null’ to a true NULL.) You only need to create the view once, of course. However, you MUST call the stored procedure IMMEDIATELY PRIOR TO selecting from this view EVERY time you use the view. Remember, the call to RESULT_SCAN() in the view is pulling from the most recent statement, which must be the CALL!

CREATE VIEW stproc_view (col_i, col_v) AS 
  SELECT NULLIF(VALUE:COL_I::VARCHAR, 'null'::VARCHAR), 
         NULLIF(value:COL_V::VARCHAR, 'null'::VARCHAR)
    FROM (SELECT $1:key1 AS tbl FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))) AS res, 
         LATERAL FLATTEN(input => res.tbl);
CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT * 
    FROM stproc_view
    ORDER BY COL_I;
+-------+---------+
| COL_I | COL_V   |
|-------+---------|
| 1     | Ariel   |
| 2     | October |
| 3     | NULL    |
| NULL  | Project |
+-------+---------+

You can even use it as a true view, i.e. select a subset of it:

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT COL_V 
    FROM stproc_view
    WHERE COL_V IS NOT NULL
    ORDER BY COL_V;
+---------+
| COL_V   |
|---------|
| Ariel   |
| October |
| Project |
+---------+

Remember, this is not a general purpose solution. There is a limit on the maximum size of VARIANT and ARRAY data types, and your entire result set must fit into a single VARIANT or ARRAY.

Protecting Privacy

This example shows a stored procedure that is useful for an on-line retailer. This stored procedure respects customers’ privacy, while protecting legitimate interests of both the retailer and the customer. If a customer asks the retailer to delete the customer’s data for privacy reasons, then this stored procedure deletes most of the customer’s data, but leaves the customer’s purchase history if either of the following is true:

  • Any purchased item has a warranty that has not yet expired.
  • The customer still owes money (or the customer is owed a refund).

(A more sophisticated version of this would delete individual rows for which payment has been made and the warranty has expired.)

Start by creating the tables and loading them:

create table reviews (customer_ID VARCHAR, review VARCHAR);
create table purchase_history (customer_ID VARCHAR, price FLOAT, paid FLOAT,
                               product_ID VARCHAR, purchase_date DATE);
insert into purchase_history (customer_ID, price, paid, product_ID, purchase_date) values 
    (1, 19.99, 19.99, 'chocolate', '2018-06-17'::DATE),
    (2, 19.99,  0.00, 'chocolate', '2017-02-14'::DATE),
    (3, 19.99,  19.99, 'chocolate', '2017-03-19'::DATE);

insert into reviews (customer_ID, review) values (1, 'Loved the milk chocolate!');
insert into reviews (customer_ID, review) values (2, 'Loved the dark chocolate!');

Create the stored procedure:

create or replace procedure delete_nonessential_customer_data(customer_ID varchar)
    returns varchar not null
    language javascript
    as
    $$

    // If the customer posted reviews of products, delete those reviews.
    var sql_cmd = "DELETE FROM reviews WHERE customer_ID = " + CUSTOMER_ID;
    snowflake.execute( {sqlText: sql_cmd} );

    // Delete any other records not needed for warranty or payment info.
    // ...

    var result = "Deleted non-financial, non-warranty data for customer " + CUSTOMER_ID;

    // Find out if the customer has any net unpaid balance (or surplus/prepayment).
    sql_cmd = "SELECT SUM(price) - SUM(paid) FROM purchase_history WHERE customer_ID = " + CUSTOMER_ID;
    var stmt = snowflake.createStatement( {sqlText: sql_cmd} );
    var rs = stmt.execute();
    // There should be only one row, so should not need to iterate.
    rs.next();
    var net_amount_owed = rs.getColumnValue(1);

    // Look up the number of purchases still under warranty...
    var number_purchases_under_warranty = 0;
    // Assuming a 1-year warranty...
    sql_cmd = "SELECT COUNT(*) FROM purchase_history ";
    sql_cmd += "WHERE customer_ID = " + CUSTOMER_ID;
    sql_cmd += "AND PURCHASE_DATE > dateadd(year, -1, current_date())";
    var stmt = snowflake.createStatement( {sqlText: sql_cmd} );
    var rs = stmt.execute();
    // There should be only one row, so should not need to iterate.
    rs.next();
    number_purchases_under_warranty = rs.getColumnValue(1);

    // Check whether need to keep some purchase history data; if not, then delete the data.
    if (net_amount_owed == 0.0 && number_purchases_under_warranty == 0)  {
        // Delete the purchase history of this customer ...
        sql_cmd = "DELETE FROM purchase_history WHERE customer_ID = " + CUSTOMER_ID;
        snowflake.execute( {sqlText: sql_cmd} );
        // ... and delete anything else that that should be deleted.
        // ...
        result = "Deleted all data, including financial and warranty data, for customer " + CUSTOMER_ID;
        }
    return result;
    $$
    ;

Show the data in the tables before we delete any of that data:

SELECT * FROM reviews;
+-------------+---------------------------+
| CUSTOMER_ID | REVIEW                    |
|-------------+---------------------------|
| 1           | Loved the milk chocolate! |
| 2           | Loved the dark chocolate! |
+-------------+---------------------------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
| 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
+-------------+-------+-------+------------+---------------+

Customer #1 has a warranty that is still in effect. The stored procedure deletes the review comments that he posted, but keeps his purchase record because of the warranty:

call delete_nonessential_customer_data(1);
+---------------------------------------------------------+
| DELETE_NONESSENTIAL_CUSTOMER_DATA                       |
|---------------------------------------------------------|
| Deleted non-financial, non-warranty data for customer 1 |
+---------------------------------------------------------+
SELECT * FROM reviews;
+-------------+---------------------------+
| CUSTOMER_ID | REVIEW                    |
|-------------+---------------------------|
| 2           | Loved the dark chocolate! |
+-------------+---------------------------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
| 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
+-------------+-------+-------+------------+---------------+

Customer #2 still owes money. The stored procedure deletes her review comments, but keeps her purchase record.

call delete_nonessential_customer_data(2);
+---------------------------------------------------------+
| DELETE_NONESSENTIAL_CUSTOMER_DATA                       |
|---------------------------------------------------------|
| Deleted non-financial, non-warranty data for customer 2 |
+---------------------------------------------------------+
SELECT * FROM reviews;
+-------------+--------+
| CUSTOMER_ID | REVIEW |
|-------------+--------|
+-------------+--------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
| 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
+-------------+-------+-------+------------+---------------+

Customer #3 does not owe any money (and is not owed any money). Her warranty expired, so the stored procedure deletes both the review comments and the purchase records.

call delete_nonessential_customer_data(3);
+-------------------------------------------------------------------------+
| DELETE_NONESSENTIAL_CUSTOMER_DATA                                       |
|-------------------------------------------------------------------------|
| Deleted all data, including financial and warranty data, for customer 3 |
+-------------------------------------------------------------------------+
SELECT * FROM reviews;
+-------------+--------+
| CUSTOMER_ID | REVIEW |
|-------------+--------|
+-------------+--------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
+-------------+-------+-------+------------+---------------+

Session Variables and Caller’s Rights vs. Owner’s Rights Stored Procedures

This example shows one of the differences between caller’s rights stored procedures and owner’s rights stored procedures. This example attempts to use session variables in two ways:

  • Set a session variable before calling the stored procedure, then use the session variable inside the stored procedure.
  • Set a session variable inside the stored procedure, then use the session variable after returning from the stored procedures.

Both using the session variable and setting the session variable work correctly in a caller’s rights stored procedure. Both fail when using an owner’s rights stored procedure even if the caller is the owner.

Caller’s Rights Stored Procedure

Create and load a table:

create table sv_table (f float);
insert into sv_table (f) values (49), (51);

Set a session variable:

set SESSION_VAR1 = 50;

Create a caller’s rights stored procedure that uses one session variable and sets another:

create procedure session_var_user()
  returns float
  language javascript
  EXECUTE AS CALLER
  as
  $$
  // Set the second session variable
  stmt = snowflake.createStatement(
      {sqlText: "set SESSION_VAR2 = 'I was set inside the StProc.'"}
      );
  rs = stmt.execute();  // we ignore the result in this case
  // Run a query using the first session variable
  stmt = snowflake.createStatement(
      {sqlText: "select f from sv_table where f > $SESSION_VAR1"}
      );
  rs = stmt.execute();
  rs.next();
  output = rs.getColumnValue(1);
  return output;
  $$
  ;

Call the procedure:

CALL session_var_user();
+------------------+
| SESSION_VAR_USER |
|------------------|
|               51 |
+------------------+

View the value of the session variable set inside the stored procedure:

SELECT $SESSION_VAR2;
+------------------------------+
| $SESSION_VAR2                |
|------------------------------|
| I was set inside the StProc. |
+------------------------------+

Although you can set a session variable inside a stored procedure and leave it set after the end of the procedure, this is usually not a good idea.

Owner’s Rights Stored Procedure

Create an owner’s rights stored procedure that uses a session variable:

create procedure cannot_use_session_vars()
  returns float
  language javascript
  EXECUTE AS OWNER
  as
  $$
  // Run a query using the first session variable
  stmt = snowflake.createStatement(
      {sqlText: "select f from sv_table where f > $SESSION_VAR1"}
      );
  rs = stmt.execute();
  rs.next();
  output = rs.getColumnValue(1);
  return output;
  $$
  ;

Call the procedure (it should fail):

100183 (P0000): Execution error in store procedure CANNOT_USE_SESSION_VARS:
Use of session variable '$SESSION_VAR1' is not allowed in owners rights stored procedure
At Statement.execute, line 6 position 12

Create an owner’s rights stored procedure that tries to set a session variable:

create procedure cannot_set_session_vars()
  returns float
  language javascript
  EXECUTE AS OWNER
  as
  $$
  // Set the second session variable
  stmt = snowflake.createStatement(
      {sqlText: "set SESSION_VAR2 = 'I was set inside the StProc.'"}
      );
  rs = stmt.execute();  // we ignore the result in this case
  return 3.0;   // dummy value.
  $$
  ;

Call the procedure (it should fail):

100183 (P0000): Execution error in store procedure CANNOT_SET_SESSION_VARS:
Stored procedure execution error: Unsupported statement type 'SET'.
At Statement.execute, line 6 position 12

Troubleshooting

A general troubleshooting technique is to use a JavaScript try/catch block to catch the error and display error information. The error object contains:

  • Error code.
  • Error message.
  • Error state.
  • Stack trace at the point of failure.

For an example of using this information, see Catching an Error using Try/Catch.

Below are some suggestions to help debug specific problems:

Stored Procedure or UDF Unexpectedly Returns NULL

Cause:

Your stored procedure/UDF has a parameter, and inside the procedure/UDF, the parameter is referred to by its lowercase name, but Snowflake has automatically converted the name to uppercase.

Solution:

Either:

  • Use uppercase for the variable name inside the JavaScript code, or
  • Enclose the variable name in double quotes in the SQL code.

For more details, see JavaScript Arguments and Returned Values.

“Failed: empty argument passed”

Cause:Your stored procedure might contain “sqltext” when it should have “sqlText” (the first is all lower-case; the second is mixed case).
Solution:Use “sqlText”.

Stored Procedure Never Finishes Running

Cause:You might have an infinite loop in your JavaScript code.
Solution:Check for and fix any infinite loops.

Error: JavaScript out of memory error: UDF thread memory limit exceeded

Cause:You might have an infinite loop in your JavaScript code.
Solution:Check for and fix any infinite loops. In particular, ensure that you stop calling for the next row when the result set runs out (i.e. when resultSet.next() returns false).