Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

How to Use JavaScript in Snowflake: Syntax and Examples

You can use JavaScript in Snowflake by writing stored procedures that combine SQL and JavaScript code. These procedures let you run complex logic inside Snowflake using the CREATE PROCEDURE statement with LANGUAGE JAVASCRIPT.
📐

Syntax

To use JavaScript in Snowflake, you create a stored procedure with the CREATE PROCEDURE command. You specify LANGUAGE JAVASCRIPT and write your JavaScript code inside a $$ block. You can use snowflake.createStatement() to run SQL commands inside the JavaScript.

  • CREATE PROCEDURE: Defines the procedure name and parameters.
  • LANGUAGE JAVASCRIPT: Specifies JavaScript as the language.
  • $$ ... $$: Delimiters for the JavaScript code block.
  • snowflake.createStatement(): Creates SQL statements to run from JavaScript.
sql
CREATE OR REPLACE PROCEDURE procedure_name(param1 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
  // JavaScript code here
  var stmt = snowflake.createStatement({sqlText: `SELECT CURRENT_DATE`});
  var resultSet = stmt.execute();
  resultSet.next();
  return 'Done';
$$;
💻

Example

This example shows a simple JavaScript stored procedure that returns the current date from Snowflake. It runs a SQL query inside JavaScript and returns the result as a string.

sql
CREATE OR REPLACE PROCEDURE get_current_date()
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
  var stmt = snowflake.createStatement({sqlText: `SELECT CURRENT_DATE`});
  var resultSet = stmt.execute();
  resultSet.next();
  var currentDate = resultSet.getColumnValue(1);
  return currentDate.toString();
$$;

-- Call the procedure
CALL get_current_date();
Output
2024-06-01
⚠️

Common Pitfalls

Common mistakes when using JavaScript in Snowflake include:

  • Not using snowflake.createStatement() properly to run SQL.
  • Forgetting to call resultSet.next() before reading results.
  • Mixing SQL and JavaScript syntax incorrectly.
  • Not handling asynchronous behavior because Snowflake JavaScript is synchronous.

Always test your stored procedure step-by-step to catch these errors.

sql
/* Wrong: Missing resultSet.next() */
CREATE OR REPLACE PROCEDURE wrong_proc()
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
  var stmt = snowflake.createStatement({sqlText: `SELECT CURRENT_DATE`});
  var resultSet = stmt.execute();
  // resultSet.next(); is missing
  var date = resultSet.getColumnValue(1);
  return date.toString();
$$;

/* Right: Include resultSet.next() */
CREATE OR REPLACE PROCEDURE right_proc()
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
  var stmt = snowflake.createStatement({sqlText: `SELECT CURRENT_DATE`});
  var resultSet = stmt.execute();
  resultSet.next();
  var date = resultSet.getColumnValue(1);
  return date.toString();
$$;
📊

Quick Reference

Here is a quick reference for using JavaScript in Snowflake stored procedures:

ConceptDescription
CREATE PROCEDUREDefines a stored procedure with JavaScript code
LANGUAGE JAVASCRIPTSpecifies JavaScript as the procedure language
snowflake.createStatement()Creates a SQL statement to execute
statement.execute()Runs the SQL statement and returns results
resultSet.next()Moves to the first row of the result set
resultSet.getColumnValue(n)Gets the value of the nth column in the current row
CALL procedure_name()Runs the stored procedure
✅

Key Takeaways

Use CREATE PROCEDURE with LANGUAGE JAVASCRIPT to write JavaScript in Snowflake.
Use snowflake.createStatement() and execute() to run SQL inside JavaScript.
Always call resultSet.next() before accessing query results.
JavaScript in Snowflake stored procedures runs synchronously.
Test stored procedures carefully to avoid common mistakes.