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 with procedural logic.

In this Topic:

Creating a Stored Procedure

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 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 contains code 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 the 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 an example that retrieves a ResultSet and iterates through it:

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: 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...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

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 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 a SQL data type; when it is stored inside a JavaScript variable inside the stored procedure, it must be converted.

  • When retrieving a value from a ResultSet object into a JavaScript variable. The ResultSet holds the value as a 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 a SQL data type.

  • When dynamically constructing a 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 Snowflake SQL data types and the corresponding JavaScript data types:

SQL Data Type

JavaScript Data Type

Notes

ARRAY

JSON

BOOLEAN

boolean

DATE

date

REAL, FLOAT, FLOAT8, FLOAT4, DOUBLE, DOUBLE PRECISION

number

TIME

string

TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ

date or SfDate

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

VARCHAR, CHAR, CHARACTER, STRING, TEXT

string

VARIANT

JSON

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

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

The table below shows appropriate conversions for the incompatible SQL data types:

Incompatible SQL Data Type

Compatible SQL Data Type

INTEGER

FLOAT

NUMBER, NUMERIC, DECIMAL

FLOAT

Converting from JavaScript to SQL

When Returning Values

The return type of a stored procedure is declared in 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 data type, can be hidden by this behavior.)

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

When Binding Values

When you bind JavaScript variables to SQL statements, Snowflake converts from the JavaScript data types to the SQL data types. You can bind variables of the following JavaScript data types:

  • number.

  • string.

  • SfDate. (For more details about the SfDate data type, which is not a standard JavaScript data type, see the Stored Procedures API.)

Additional Information about Data Type Conversion

You might also find the following topics 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 other SQL statements, a CALL statement runs within a session, and inherits context from that session, such as session-level variables, current database, etc. 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.

Changes that the stored procedure makes to the session can persist after the end of the CALL, if the stored procedure is a caller’s rights stored procedure. (Owner’s rights stored procedures are not permitted to change session state.)

The creator of the stored procedure chooses at creation time whether to make the stored procedure an owner’s rights stored procedure or a caller’s rights stored procedure. The default is owner’s rights.

Caller’s Rights Stored Procedures

Caller’s rights stored procedures adhere to the following rules within a session:

  • Run with the privileges of the caller, not the privileges of the owner.

  • Inherit the current warehouse of the caller.

  • Use the database and schema that the caller is currently using.

  • Can view, set, and unset the caller’s session variables.

  • Can view, set, and unset the caller’s session parameters.

The remainder of this section provides more details about how caller’s rights stored procedures can read and write the caller’s session-level variables.

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.
                      -- Executes SET_SESSION_VAR2 to set 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.

  • The statements after the stored procedure can see the variable that was set inside the procedure.

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

Often, this behavior is desirable. For example, in many stored procedures, you 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 not likely 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);

Important

If you are familiar with programming in languages such as C, Java, etc., note 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 requires more effort in SQL than in C.

Owner’s Rights Stored Procedures

Owner’s rights stored procedures adhere to the following rules within a session:

  • Run with the privileges of the owner, not the privileges of the caller.

  • Inherit the current warehouse of the caller.

  • Use the database and schema that the stored procedure is created in, not the database and schema that the caller is currently using.

  • Cannot access most caller-specific information. For example:

    • Cannot view, set, or unset the caller’s session variables.

    • Can only read supported session parameters, and cannot set or unset any of the caller’s session parameters.

    • Cannot call functions such as CURRENT_USER().

    • Cannot query INFORMATION_SCHEMA table functions, such as AUTOMATIC_CLUSTERING_HISTORY, that return results based on the current user.

  • Do not allow non-owners to view information about the procedure from the PROCEDURES view.

Naming Conventions for Stored Procedures

Stored procedures are database objects, meaning that they are created in a specified database and schema. 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. Multiple stored procedures in the same schema can have the same name, as long as their 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 in 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

A stored procedure can be called entirely inside a transaction, or outside any transaction. However, transaction control commands (BEGIN, COMMIT, ROLLBACK) are not allowed inside a stored procedure.

The following pseudo-code shows a stored procedure called entirely inside a transaction:

BEGIN;
W;
CALL MyProcedure();   -- executes X and Y
Z;
COMMIT;

If a stored procedure is called outside an explicit transaction, then each statement inside the stored procedure executes as a separate transaction.

Remember that DDL statements (CREATE TABLE, etc.) cause an implicit COMMIT. Such statements should not be used inside a stored procedure if the procedure will be called inside a transaction. For example, the following pseudo-code demonstrates what not to do:

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;

General Tips

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

Symmetric Code

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 you set up.
pop b;
pop a;

You might want to use this approach in your stored procedures:

  • If a stored procedure makes temporary changes to your session, then that procedure should undo those changes before returning.

  • If a stored procedure utilizes 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 your code might look similar to the pseudo-code shown below:

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;
    $$
    ;

Line Continuation

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;"
    

Calling a Stored Procedure

To execute a stored procedure, use a CALL statement. For example:

call stproc1(5.14::FLOAT);

Each argument to a stored procedure can be a general expression:

CALL stproc1(2 * 5.14::FLOAT);

An argument can be a subquery:

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

You can call only one stored procedure per CALL statement. For example, the following statement fails:

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

Also, you cannot use a stored procedure CALL as part of an expression. For example, all the following statements 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 is shown in the Examples section (in this topic).

Caution

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

Access Control Privileges

Stored Procedures utilize two types of privileges:

  • 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. Note that 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 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 delegates 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 the GET_DDL function to get the stored procedure’s DDL.

  • Ability to call the CURRENT_USER function.

  • 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 (see list below).

If a statement inside a stored procedure references an unsupported 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 the account parameter.

The list of supported parameters currently includes (the list might change over time):

  • 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 temporary tables (i.e., tables that are automatically dropped at the end of the session).

The reason for this restriction is that the temporary table should exist only until the end of the caller’s session, but information about temporary 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, the following 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 (e.g., 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.

The stored procedure can throw a pre-defined exception or a custom exception. A simple example of throwing a custom exception is here.

Restrictions on Stored Procedures

Stored procedures have the following restrictions:

  • 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).

  • 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.

  • In rare cases, calling too many stored procedures at the same time can cause a deadlock.

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:

In the example below, the first assignment statement is incorrect because the name argument1 is in lower case.

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

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 either single quotes or double dollar signs ($$).

Using $$ makes it easier to handle JavaScript code that contains single quotes without “escaping” those quotes.

Binding Variables

Binding a variable to a SQL statement allows you to use the value of the variable in the statement. The data type of the variable should be appropriate for the use of the value in the SQL statement. (For details about the mapping between SQL data types and JavaScript data types, see SQL and JavaScript Data Type Mapping.)

You can bind NULL values as well as non-NULL values.

This shows how to bind a VARCHAR and a TIMESTAMP_LTZ variable to an INSERT statement.

Create a table.

CREATE TABLE table1 (v VARCHAR, ts1 TIMESTAMP_LTZ(9));

Create a stored procedure. This procedure accepts a VARCHAR, and converts the VARCHAR to a TIMESTAMP_LTZ by using SQL. The procedure then retrieves the converted value from a ResultSet. The value is stored in a JavaScript variable of type SfDate. The stored procedure then binds both the original VARCHAR and the TIMESTAMP_LTZ to an INSERT statement. This also demonstrates binding of JavaScript numeric data.

CREATE OR REPLACE PROCEDURE string_to_timestamp_ltz(TSV VARCHAR) 
RETURNS TIMESTAMP_LTZ 
LANGUAGE JAVASCRIPT 
AS 
$$ 
    // Convert the input varchar to a TIMESTAMP_LTZ.
    var sql_command = "SELECT '" + TSV + "'::TIMESTAMP_LTZ;"; 
    var stmt = snowflake.createStatement( {sqlText: sql_command} ); 
    var resultSet = stmt.execute(); 
    resultSet.next(); 
    // Retrieve the TIMESTAMP_LTZ and store it in an sfDate variable.
    my_sfDate = resultSet.getColumnValue(1); 

    // Specify that we'd like position-based binding.
    sql_command = `INSERT INTO table1 VALUES(:1, :2);` 
    // Bind a VARCHAR and a TIMESTAMP_LTZ to our INSERT statement.
    result = snowflake.execute(
        { sqlText: sql_command, binds: [TSV, my_sfDate] }
        ); 

    return my_sfDate; 
$$ ; 

Call the procedure.

CALL string_to_timestamp_ltz('2008-11-18 16:00:00');
+-------------------------------+
| STRING_TO_TIMESTAMP_LTZ       |
|-------------------------------|
| 2008-11-18 16:00:00.000 -0800 |
+-------------------------------+

Verify that the row was inserted.

SELECT * FROM table1;
+---------------------+-------------------------------+
| V                   | TS1                           |
|---------------------+-------------------------------|
| 2008-11-18 16:00:00 | 2008-11-18 16:00:00.000 -0800 |
+---------------------+-------------------------------+

For additional examples of binding data in JavaScript, see Binding Statement Parameters.

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 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 a SQL Statement 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 can dynamically create a SQL statement and execute it. However, this can allow 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 an example of binding variables, see Binding Variables.

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 (e.g., 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:

create or replace procedure sp_pi()
    returns float not null
    language javascript
    as
    $$
    return 3.1415926;
    $$
    ;

Note that the $$ delimiter marks the beginning and end of the JavaScript code.

Now call the procedure you just created:

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 inserts a row into an existing table named stproc_test_table1 and returns the value “Succeeded.”. The returned value is not particularly useful from a SQL perspective, but it allows you to return status information (e.g., “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 (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

The following example shows a basic, but not particularly realistic, 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 a SQL Statement

The following example shows how to dynamically create a SQL statement:

Note

As stated in SQL Injection (in this topic), be careful to guard against attacks when using dynamic SQL.

  1. Create the stored procedure. This procedure allows you to pass the name of a table and get the number of rows in that table (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;
        $$
        ;
    
  2. Call the stored procedure:

    call get_row_count('stproc_test_table1');
    +---------------+
    | GET_ROW_COUNT |
    |---------------|
    |             3 |
    +---------------+
    
  3. Show the results from select count(*) for the same table:

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

Retrieving Result Set Metadata

This example demonstrates 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 example demonstrates using a JavaScript try/catch block to catch an error inside a stored procedure:

  1. 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;
          $$
          ;
    
  2. 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                |
    +---------------------------------------------------------+
    

The following example demonstrates throwing a custom exception:

  1. Create the stored procedure:

    CREATE OR REPLACE PROCEDURE validate_age (age float)
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS $$
        try {
            if (AGE < 0) {
                throw "Age cannot be negative!";
            } else {
                return "Age validated.";
            }
        } catch (err) {
            return "Error: " + err;
        }
    $$;
    
  2. Call the stored procedure with valid and invalid values:

    CALL validate_age(50);
    +----------------+
    | VALIDATE_AGE   |
    |----------------|
    | Age validated. |
    +----------------+
    CALL validate_age(-2);
    +--------------------------------+
    | VALIDATE_AGE                   |
    |--------------------------------|
    | Error: Age cannot be negative! |
    +--------------------------------+
    

Logging an Error

The following example logs errors to a table. In a more realistic example, you would probably also include the timestamp of the time that the error occurred (using the CURRENT_TIMESTAMP function).

CREATE OR REPLACE TABLE error_log (error_code number, error_state string, error_message string, stack_trace string);

CREATE OR REPLACE PROCEDURE broken() 
RETURNS varchar 
NOT NULL 
LANGUAGE javascript 
AS $$
var result;
try {
    snowflake.execute({ sqlText: "Invalid Command!;" });
    result = "Succeeded";
} catch (err) {
    result = "Failed";
    snowflake.execute({
      sqlText: `insert into error_log VALUES (?,?,?,?)`
      ,binds: [err.code, err.state, err.message, err.stackTraceTxt]
      });
}
return result;
$$;
call broken();
+--------+
| BROKEN |
|--------|
| Failed |
+--------+
select * from error_log;
+------------+-------------+---------------------------------------------------------+------------------------------------------+
| ERROR_CODE | ERROR_STATE | ERROR_MESSAGE                                           | STACK_TRACE                              |
|------------+-------------+---------------------------------------------------------+------------------------------------------|
|     100183 | P0000       | SQL compilation error:                                  | At Snowflake.execute, line 4 position 14 |
|            |             | syntax error line 1 at position 0 unexpected 'Invalid'. |                                          |
+------------+-------------+---------------------------------------------------------+------------------------------------------+

Overloading Stored Procedure Names

As described in Naming Conventions for Stored Procedures (in this topic), you can overload stored procedure names. For example:

  1. 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;
        $$
        ;
    
  2. Next, call the two procedures:

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

Note

You must also specify the data types of the arguments for some other operations on stored procedures. For example, GRANT and REVOKE require the argument types, as well as the stored procedure name.

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 statement:

  1. Create the table:

    CREATE TABLE western_provinces(ID INT, province VARCHAR);
    
  2. 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;
      $$
      ;
    
  3. 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 perform more complex operations on the value returned by the RESULT_SCAN function. In this case, because 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 previous example, illustrates one way to do this:

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

    CREATE TABLE all_provinces(ID INT, province VARCHAR);
    
  2. 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 |
    +-------------------------+
    
  3. Verify 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:

  1. Create a table named one_string. This table temporarily stores the result of the CALL command. The result of the CALL is a single string, so this table stores only a single VARCHAR value.

    CREATE TRANSIENT TABLE one_string(string_col VARCHAR);
    
  2. 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 is actually a single 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 to extract multiple rows from the 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         |
    +---------------------+
    
  3. Next, create a table named three_strings. This table will hold the result after you’ve split it into individual lines/strings:

    CREATE TRANSIENT TABLE three_strings(string_col VARCHAR);
    
  4. Now convert that one string in the one_string table into three separate strings, and show that it is now actually three strings:

    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 |
    +----------+
    
  5. Now convert the 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 |
    +-------------------------+
    
  6. 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 and you might want to return a status/error message for each SQL statement. However, a stored procedure returns a single row; it is not designed to return multiple rows.

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 additional effort.

The following example shows one way to do this (the error messages shown are not real, 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 described in 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 return a result set from a stored procedure with some additional code:

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 a single 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. 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 real-world version of this would delete individual rows for which payment has been made and the warranty has expired.

  1. 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!');
    
  2. 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;
        // Can't use CURRENT_DATE() because that changes. So assume that today is 
        // always June 15, 2019.
        sql_cmd += "AND PURCHASE_DATE > dateadd(year, -1, '2019-06-15'::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;
        $$
        ;
    
  3. Show the data in the tables before deleting 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    |
    +-------------+-------+-------+------------+---------------+
    
  4. Customer #1 has a warranty that is still in effect. The stored procedure deletes the review comments that they posted, but keeps their 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    |
    +-------------+-------+-------+------------+---------------+
    
  5. Customer #2 still owes money. The stored procedure deletes their review comments, but keeps their 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    |
    +-------------+-------+-------+------------+---------------+
    
  6. Customer #3 does not owe any money (and is not owed any money). Their 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    |
    +-------------+-------+-------+------------+---------------+
    

Using Session Variables with Caller’s Rights and Owner’s Rights Stored Procedures

These examples illustrate one of the key differences between caller’s rights and owner’s rights stored procedures. They attempt 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

The following example demonstrates a caller’s rights stored procedure.

  1. Create and load a table:

    create table sv_table (f float);
    insert into sv_table (f) values (49), (51);
    
  2. Set a session variable:

    set SESSION_VAR1 = 50;
    
  3. 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;
      $$
      ;
    
  4. Call the procedure:

    CALL session_var_user();
    +------------------+
    | SESSION_VAR_USER |
    |------------------|
    |               51 |
    +------------------+
    
  5. View the value of the session variable set inside the stored procedure:

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

Note

Although you can set a session variable inside a stored procedure and leave it set after the end of the procedure, Snowflake does not recommend doing this.

Owner’s Rights Stored Procedure

The following example demonstrates an owner’s rights stored procedure.

  1. 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;
      $$
      ;
    
  2. 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
    
  3. 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.
      $$
      ;
    
  4. 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 more information, including an example, of how to use this information, see Catching an Error using Try/Catch (in this topic).

Th following sections provide additional 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.

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: 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”.

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).