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:
| Concept | Description |
|---|---|
| CREATE PROCEDURE | Defines a stored procedure with JavaScript code |
| LANGUAGE JAVASCRIPT | Specifies 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.
