Categories:
UDF (User-Defined Function) & Stored Procedure DDL

CREATE PROCEDURE

Creates a new stored procedure. A stored procedure contains JavaScript code.

See also:

ALTER PROCEDURE , DESCRIBE PROCEDURE , DROP PROCEDURE , SHOW PROCEDURES

CALL

Syntax

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS <result_data_type> [ NOT NULL ]
  LANGUAGE JAVASCRIPT
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]
  AS '<procedure_definition>'

Important

JavaScript is case-sensitive, whereas SQL is not. See Case-sensitivity in JavaScript Arguments for important information about using stored procedure argument names in the JavaScript code.

Required Parameters

name ( [ arg_name arg_data_type ] [ , ... ] )

Specifies the identifier (and optionally one or more arguments/inputs) for the stored procedure. The identifier does not need to be unique for the schema in which the procedure is created because stored procedures are identified and resolved by their name and argument types.

However, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier Syntax.

RETURNS result_data_type [ NOT NULL ]

Specifies the results returned by the stored procedure.

As a practical matter, the returned value cannot be used because the call cannot be part of an expression.

LANGUAGE JAVASCRIPT
Specifies that the stored procedure contains JavaScript code. Currently, JavaScript is the only language supported; specifying any other language will result in an error message.
AS procedure_definition

Defines the JavaScript code executed by the stored procedure. The definition can consist of any valid JavaScript; however, Snowflake does not validate the code at stored procedure creation time (i.e. creation of the stored procedure succeeds regardless of whether the code is valid). If the code is not valid, errors will be returned when the stored procedure is called.

The delimiters around the procedure definition can be either single quotes or a pair of dollar signs. Using “$$” as the delimiter makes it easier to write stored procedures that contain single quotes.

JavaScript allows backquotes (also called “backticks”) around strings that contain newlines.

The example below uses “$$” and backquotes because the body of the stored procedure contains single quotes and double quotes:

CREATE OR REPLACE TABLE table1 ("column 1" VARCHAR);
CREATE or replace PROCEDURE proc3()
  RETURNS VARCHAR
  LANGUAGE javascript
  AS
  $$
  rs = snowflake.execute( { sqlText: 
      `INSERT INTO table1 ("column 1") 
           SELECT 'value 1' AS "column 1" ;`
       } );
  return 'Done.';
  $$;

For more details about stored procedures, see:

Optional Parameters

CALLED ON NULL INPUT or . RETURNS NULL ON NULL INPUT | STRICT

Specifies the behavior of the stored procedure when called with null inputs. In contrast to system-defined functions, which always return null when any input is null, stored procedures can handle null inputs, returning non-null values even when an input is null:

  • CALLED ON NULL INPUT will always call the stored procedure with null inputs. It is up to the procedure to handle such values appropriately.
  • RETURNS NULL ON NULL INPUT (or its synonym STRICT) will not call the stored procedure if any input is null, so the statements inside the stored procedure will not be executed. Instead, a null value will always be returned. Note that the procedure might still return null for non-null inputs.

Default: CALLED ON NULL INPUT

VOLATILE | IMMUTABLE

Specifies the behavior of the stored procedure when returning results:

  • VOLATILE: The procedure might return different values for different rows, even for the same input (e.g. due to non-determinism and statefullness).
  • IMMUTABLE: The procedure assumes that, when called with the same inputs, it will always return the same result. This guarantee is not checked. Specifying IMMUTABLE for a procedure that returns different values for the same input will result in undefined behavior.

Default: VOLATILE

COMMENT = 'string_literal'

Specifies a comment for the stored procedure, which is displayed in the DESCRIPTION column in the SHOW PROCEDURES output.

Default: stored procedure

EXECUTE AS CALLER | OWNER

A stored procedure can execute with the privileges of the owner (an “owner’s rights” stored procedure) or with the privileges of the caller (a “caller’s rights” stored procedure. If you execute the statement CREATE PROCEDURE ... EXECUTE AS CALLER, then int he future the procedure will execute as a caller’s rights procedure. If you execute CREATE PROCEDURE ... EXECUTE AS OWNER, then the procedure will execute as an owner’s rights procedure. By default (if neither OWNER nor CALLER is specified explicitly at the time the procedure is created), the procedure runs as an owner’s rights stored procedure. Owner’s rights stored have less access to the caller’s environment (for example the caller’s session variables), and Snowflake defaults to this higher level of privacy and security. For more information about owner’s vs. caller’s rights procedures, see Privileges on the Database Objects Accessed by the Stored Procedure.

Default: OWNER

Usage Notes

  • A stored procedure can return only a single value, such as a string (for example, a success/failure indicator) or a number (for example, an error code). If you need to return more extensive information, you can return a VARCHAR that contains values separated by a delimiter (such as a comma), or a semi-structured data type, such as VARIANT.
  • Stored procedures support overloading. Two procedures can have the same name if they have a different number of parameters or different data types for their parameters.

Tip

If your organization uses a mix of caller’s rights and owner’s rights stored procedures, you might want to use a naming convention for your stored procedures to indicate whether an individual stored procedure is a caller’s rights stored procedure or an owner’s rights stored procedure.

Examples

This creates a trivial stored procedure that returns a hard-coded value. This is unrealistic, but shows the basic SQL syntax with minimal JavaScript code:

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

This shows a more realistic example that includes a call to the JavaScript API. A more extensive version of this procedure could allow a user to insert data into a table that the user didn’t have privileges to insert into directly. JavaScript statements could check the input parameters and execute the SQL INSERT only if certain requirements were met.

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

For more examples, see Working with Stored Procedures.