This topic explains how to write the JavaScript code for a stored procedure.
Note
To both create and call an anonymous procedure, use CALL (with anonymous procedure). Creating and calling an anonymous procedure does
not require a role with CREATE PROCEDURE schema privileges.
You can capture log and trace data as your handler code executes. For more information, refer to
Logging, tracing, and metrics.
The JavaScript API for stored procedures is similar to, but not identical to, the APIs in Snowflake connectors and drivers
(Node.js, JDBC, Python, etc.).
The API enables you to perform operations such as:
Execute a SQL statement.
Retrieve the results of a query (i.e. a result set).
Retrieve 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.
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();
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:
CREATEORREPLACEPROCEDURE read_result_set()RETURNSFLOATNOTNULLLANGUAGEJAVASCRIPTAS$$
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.
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 prepared statement.
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;"
The range for numbers with precision intact is from
-(2^53 -1)
to
(2^53 -1)
The range of valid values in Snowflake NUMBER(p, s) and DOUBLE data types is larger. Retrieving a value from Snowflake
and storing it in a JavaScript numeric variable can result in loss of precision. For example:
select hash(1) AS a,
num_test(hash(1)) AS b,
a - b;
+----------------------+----------------------+------------+
| A | B | A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+
The first two columns should match, and the third should contain 0.0.
The problem applies to JavaScript user-defined functions (UDFs) and stored procedures.
If you experience the problem in stored procedures when using getColumnValue(), you might be able to avoid the
problem by retrieving a value as a string, e.g. with:
getColumnValueAsString();
You can then return the string from the stored procedure, and cast the string to a numeric data type in SQL.
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.
You can execute your SQL statements inside a try block. If an error occurs, then your catch block can roll back all of
the statements (if you put the statements in a transaction). The Examples section contains an example of
rolling back a transaction in a stored procedure.
Stored procedures have the following restrictions:
The JavaScript code cannot call the JavaScript eval() function.
JavaScript stored procedures support access to the standard JavaScript library. Note that this excludes many objects and methods
typically provided by browsers. There is no mechanism to import, include, or call additional libraries.
Allowing 3rd-party libraries could create security holes.
JavaScript code is executed within a restricted engine, preventing system calls from the JavaScript
context (e.g. no network and disk access), and constraining the system resources available to the engine, specifically memory.
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.
Binding a variable to a SQL statement allows you to use the value of
the variable in the statement.
You can bind NULL values as well as non-NULL values.
The data type of the variable should be appropriate for the use of the value in the SQL statement. Currently, only
JavaScript variables of type number, string, and SfDate can be bound. (For details about the
mapping between SQL data types and JavaScript data types, see SQL and JavaScript data type mapping.)
Here is a short example of binding:
var stmt = snowflake.createStatement(
{
sqlText: "INSERT INTO table2 (col1, col2) VALUES (?, ?);",
binds:["LiteralValue1", variable2]
}
);
Here is a more complete example. This example binds TIMESTAMP information. Because direct binding of SQL TIMESTAMP
data is not supported, this example passes the timestamp as a VARCHAR, then binds that to the statement. Note that
the SQL statement itself converts the VARCHAR to a TIMESTAMP by calling the TO_TIMESTAMP() function:
This simple function returns TRUE if the specified timestamp is prior to now, and FALSE otherwise.
This shows how to bind a VARCHAR, a TIMESTAMP_LTZ, and other data types to an INSERT statement. The
TIMESTAMP_LTZ binds an SfDate variable that is created inside the stored procedure.
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.
CREATEORREPLACEPROCEDURE string_to_timestamp_ltz(TSV VARCHAR)RETURNSTIMESTAMP_LTZLANGUAGEJAVASCRIPTAS$$
// 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.
var my_sfDate = resultSet.getColumnValue(1);
f = 3.1415926;
// Specify that we'd like position-based binding.
sql_command = `INSERT INTO table1 VALUES(:1, :2, :3, :4, :5, :6, :7, :8);`
// Bind a VARCHAR, a TIMESTAMP_LTZ, a numeric to our INSERT statement.
result = snowflake.execute(
{
sqlText: sql_command,
binds: [TSV, my_sfDate, f, f, f, my_sfDate, my_sfDate, '12:30:00.123' ]
}
);
return my_sfDate;
$$;
Snowflake limits the maximum size of the JavaScript source code in the body of a JavaScript stored procedure. Snowflake recommends
limiting the size to 100 KB. (The code is stored in a compressed form, and the exact limit depends on the compressibility of the
code.)
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.
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).
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 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 a procedure to count the number of rows in a table (equivalent to select count(*) from table):
CREATEORREPLACEPROCEDURE get_row_count(table_nameVARCHAR)RETURNSFLOATNOTNULLLANGUAGEJAVASCRIPTAS$$
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;
$$;
Ask the stored procedure how many rows are in the table:
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.
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
$$
varrow_count=0;
// Dynamically compose the SQL statement to execute.// Note that we uppercased the input parameter name.varsql_command="select count(*) from " + TABLE_NAME;
// Run the statement.varstmt= snowflake.createStatement(
{
sqlText: sql_command
}
);
varres= 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;
$$
;
createorreplaceprocedure get_column_scale(column_index float)returnsfloatnotnulllanguagejavascriptas$$
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 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!|+--------------------------------+
The following example wraps multiple related statements in a transaction, and uses try/catch to commit or roll back.
The parameter force_failure allows the caller to choose between successful execution and deliberate error.
-- Create the procedure
CREATEORREPLACEPROCEDUREcleanup(force_failure VARCHAR)
RETURNSVARCHARNOTNULLLANGUAGEJAVASCRIPTAS
$$
var result = "";
snowflake.execute( {sqlText: "BEGIN WORK;"} );
try {
snowflake.execute( {sqlText: "DELETE FROM child;"} );
snowflake.execute( {sqlText: "DELETE FROM parent;"} );
if (FORCE_FAILURE === "fail") {
// To see what happens if there is a failure/rollback,
snowflake.execute( {sqlText: "DELETE FROM no_such_table;"} );
}
snowflake.execute( {sqlText: "COMMIT WORK;"} );
result = "Succeeded";
}
catch (err) {
snowflake.execute( {sqlText: "ROLLBACK WORK;"} );
return"Failed: " + err; // Return a success/error indicator.
}
return result;
$$
;
CALLcleanup('fail');
CALLcleanup('do not fail');
You can capture log and trace data from JavaScript handler code by using the snowflake object in the JavaScript API. When you do,
log messages and trace data are stored in an event table that you can analyze with queries.
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
$$
varreturn_value="";
try {
varcommand="SELECT * FROM western_provinces ORDER BY province;"varstmt= snowflake.createStatement( {sqlText: command } );
varrs= 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*FROMTABLE(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.
Tip
You can also use the pipe operator (->>) instead of the RESULT_SCAN function to
run a CALL statement and process its result set with a single command.
The following example, which is a continuation of the previous example, illustrates one way to do this:
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:
CREATETABLE 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:
INSERTINTO all_provinces
WITH
one_string (string_col)AS(SELECT*FROMTABLE(RESULT_SCAN(LAST_QUERY_ID()))),
three_strings (one_row)AS(SELECTVALUEFROM one_string,LATERALSPLIT_TO_TABLE(one_string.string_col,'\n'))SELECTSTRTOK(one_row,',',1)AS ID,STRTOK(one_row,',',2)AS province
FROM three_strings
WHERENOT(ID ISNULLAND province ISNULL);+-------------------------+|numberofrows inserted ||-------------------------||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:
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.
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 |+------------------------+INSERTINTO one_string
SELECT*FROMTABLE(RESULT_SCAN(LAST_QUERY_ID()));+-------------------------+|numberofrows 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<<<|+---------------------+SELECTCOUNT(*)FROM one_string;+----------+|COUNT(*)||----------||1|+----------+
The following commands show how to extract multiple rows from the string:
SELECT*FROM one_string,LATERALSPLIT_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 ||||+---------------------+-----+-------+---------------------+SELECTVALUEFROM one_string,LATERALSPLIT_TO_TABLE(one_string.string_col,'\n');+---------------------+|VALUE||---------------------||1, Alberta ||2, British Columbia ||3, Manitoba |+---------------------+
Next, create a table named three_strings. This table will hold the result after you’ve split it into individual
lines/strings:
Now convert that one string in the one_string table into three separate strings, and show that it is
now actually three strings:
INSERTINTO three_strings
SELECTVALUEFROM one_string,LATERALSPLIT_TO_TABLE(one_string.string_col,'\n');+-------------------------+|numberofrows inserted ||-------------------------||3|+-------------------------+SELECT string_col
FROM three_strings;+---------------------+| STRING_COL ||---------------------||1, Alberta ||2, British Columbia ||3, Manitoba |+---------------------+SELECTCOUNT(*)FROM three_strings;+----------+|COUNT(*)||----------||3|+----------+
Now convert the three strings into three rows in our long-term table named all_provinces:
INSERTINTO all_provinces
SELECTSTRTOK(string_col,',',1)AS ID,STRTOK(string_col,',',2)AS province
FROM three_strings
WHERENOT(ID ISNULLAND province ISNULL);+-------------------------+|numberofrows 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 |+----+-------------------+SELECTCOUNT(*)FROM all_provinces;+----------+|COUNT(*)||----------||3|+----------+
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):
CREATEORREPLACEPROCEDURE sp_return_array()RETURNSVARIANTNOTNULLLANGUAGEJAVASCRIPTAS$$
// 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.SELECTINDEX,VALUEFROMTABLE(RESULT_SCAN(LAST_QUERY_ID()))AS res,LATERALFLATTEN(INPUT=> res.$1)ORDERBYindex;+-------+---------------------------------------------+|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.
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 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.varrow_as_json= {};
// This array will contain all the rows.vararray_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".vartable_as_json= {};
// Run SQL statement(s) and get a resultSet.varcommand="SELECT * FROM " + TABLE_NAME;
varcmd1_dict= {sqlText: command};
varstmt= snowflake.createStatement(cmd1_dict);
varrs= stmt.execute();
// Read each row and add it to the array we will return.varrow_num=1;
while (rs.next()) {
// Put each row in a variable of type JSON.
row_as_json = {};
// For each column in the row...for (varcol_num=0; col_num < COL_NAMES.length; col_num = col_num + 1) {
varcol_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:key1FROMTABLE(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.SELECTVALUE:COL_IAS col_i,value:COL_VAS col_v
FROMTABLE(RESULT_SCAN(LAST_QUERY_ID()))AS res,LATERALFLATTEN(input=> res.$1)ORDERBY 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:
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:
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 ISNOTNULLORDERBY 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.
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.
insertinto 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);insertinto reviews (customer_ID,review)values(1,'Loved the milk chocolate!');insertinto 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.varsql_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.// ...varresult="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;
varstmt= snowflake.createStatement( {sqlText: sql_cmd} );
varrs= stmt.execute();
// There should be only one row, so should not need to iterate.
rs.next();
varnet_amount_owed= rs.getColumnValue(1);
// Look up the number of purchases still under warranty...varnumber_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)";
varstmt= snowflake.createStatement( {sqlText: sql_cmd} );
varrs= 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 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 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|+-------------+-------+-------+------------+---------------+
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:
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:
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.
Create a caller’s rights stored procedure that uses one session variable and sets another:
createprocedure session_var_user()returnsfloatlanguagejavascriptEXECUTEASCALLERas$$
// Set the second session variable
var stmt = snowflake.createStatement(
{sqlText: "set SESSION_VAR2 = 'I was set inside the StProc.'"}
);
var 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();
var output = rs.getColumnValue(1);
return output;
$$;
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.
The following example demonstrates an owner’s rights stored procedure.
Create an owner’s rights stored procedure that uses a session variable:
createprocedure cannot_use_session_vars()returnsfloatlanguagejavascriptEXECUTEASOWNERas$$
// Run a query using the first session variable
var stmt = snowflake.createStatement(
{sqlText: "select f from sv_table where f > $SESSION_VAR1"}
);
var rs = stmt.execute();
rs.next();
var output = rs.getColumnValue(1);
return output;
$$;
Call the procedure (it should fail):
CALL cannot_use_session_vars();
Create an owner’s rights stored procedure that tries to set a session variable:
createprocedure cannot_set_session_vars()returnsfloatlanguagejavascriptEXECUTEASOWNERas$$
// Set the second session variable
var stmt = snowflake.createStatement(
{sqlText: "set SESSION_VAR2 = 'I was set inside the StProc.'"}
);
var rs = stmt.execute(); // we ignore the result in this case
return 3.0; // dummy value.
$$;
A general troubleshooting technique is to use a JavaScript try/catch block to
catch the error and display error information. The error object contains:
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.
Your stored procedure might contain “sqltext” when it should have “sqlText”
(the first is all lowercase; 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).