Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Create Stored Procedure in Snowflake: Simple Guide

To create a stored procedure in Snowflake, use the CREATE PROCEDURE statement with JavaScript code inside LANGUAGE JAVASCRIPT. Define input parameters, write your logic in JavaScript, and use CALL procedure_name() to run it.
📐

Syntax

The basic syntax to create a stored procedure in Snowflake includes naming the procedure, defining input parameters, specifying the language as JavaScript, and writing the procedure body inside a $$ block.

  • CREATE PROCEDURE: Starts the procedure creation.
  • procedure_name: Name of your procedure.
  • (parameters): Input parameters with types.
  • RETURNS: The return type, usually STRING or VOID.
  • LANGUAGE JAVASCRIPT: Specifies the procedure uses JavaScript.
  • AS $$ ... $$: JavaScript code block for procedure logic.
sql
CREATE PROCEDURE procedure_name(param1 TYPE, param2 TYPE)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS $$
  // JavaScript code here
  return 'result';
$$;
💻

Example

This example creates a stored procedure named greet_user that takes a name parameter and returns a greeting message.

sql
CREATE OR REPLACE PROCEDURE greet_user(name STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS $$
  return 'Hello, ' + name + '!';
$$;

-- Call the procedure
CALL greet_user('Alice');
Output
+---------------------+ | Greet_User('Alice') | +---------------------+ | Hello, Alice! | +---------------------+
⚠️

Common Pitfalls

Common mistakes when creating stored procedures in Snowflake include:

  • Forgetting to specify LANGUAGE JAVASCRIPT.
  • Not using CREATE OR REPLACE PROCEDURE to update existing procedures.
  • Incorrect parameter types or missing return statement.
  • Using SQL syntax inside the JavaScript block instead of JavaScript syntax.

Always test your procedure by calling it after creation.

sql
/* Wrong: Missing LANGUAGE JAVASCRIPT */
CREATE PROCEDURE bad_proc()
RETURNS STRING
AS $$
  return 'Oops';
$$;

/* Right: Specify language */
CREATE OR REPLACE PROCEDURE good_proc()
RETURNS STRING
LANGUAGE JAVASCRIPT
AS $$
  return 'All good';
$$;
📊

Quick Reference

PartDescription
CREATE PROCEDUREStarts procedure creation
procedure_name(params)Name and input parameters
RETURNS typeReturn data type
LANGUAGE JAVASCRIPTSpecifies JavaScript code
AS $$ ... $$JavaScript code block with logic
✅

Key Takeaways

Use CREATE OR REPLACE PROCEDURE with LANGUAGE JAVASCRIPT to define stored procedures in Snowflake.
Write procedure logic inside the AS $$ ... $$ block using JavaScript syntax.
Always specify input parameters and return type correctly.
Call procedures with CALL procedure_name(arguments) to test them.
Avoid mixing SQL syntax inside the JavaScript code block.