Categories:

DDL for User-Defined Functions, External Functions, and Stored Procedures

CREATE PROCEDURE

Creates a new stored procedure that is written in one of the following languages:

See also:

ALTER PROCEDURE , DESCRIBE PROCEDURE , DROP PROCEDURE , SHOW PROCEDURES

CALL

Syntax

JavaScript

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.

Snowflake Scripting

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

Required Parameters

All Languages

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

Specifies the identifier (name) and any input arguments for the stored procedure.

  • For the identifier:

    • 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 the combination of the name and argument types.

    • 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. See Identifier Requirements.

  • For each input argument:

    • For arg_name, specify the name of the input argument.

    • For arg_data_type, use the Snowflake data type that corresponds to the language that you are using.

RETURNS ...

Specifies the type of the result returned by the stored procedure.

  • For result_data_type, use the Snowflake data type that corresponds to the type of the language that you are using.

  • For TABLE ( col_name col_data_type , ... ), specify the names and types of the columns in the tabular data to be returned.

    This is supported only in Snowflake Scripting stored procedures if your procedure returns a table. See RETURN.

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

LANGUAGE language

Specifies the language of the stored procedure code. Note that this is optional for stored procedures written with Snowflake Scripting.

Currently, the supported values for language include:

Default: SQL.

AS procedure_definition

Defines the code executed by the stored procedure. The definition can consist of any valid code.

Note

For languages other than Snowflake Scripting, Snowflake does not validate the code when you execute the CREATE PROCEDURE command. If the code is not valid, the CREATE PROCEDURE command will succeed, and errors will be returned when the stored procedure is called.

If you are writing a stored procedure in JavaScript, you must use delimiters around the string literal that is the procedure definition. You can use single quotes or a pair of dollar signs. Using $$ as the delimiter makes it easier to write stored procedures that contain single quotes.

Note

For Snowflake Scripting, you do not need the delimiters unless you are using SnowSQL or the classic web interface to create the procedure. See Using Snowflake Scripting in SnowSQL and the Classic Web Interface.

If you are writing your stored procedure in JavaScript and you are writing a string that contains newlines, you can use backquotes (also called “backticks”) around the string.

The following example of a JavaScript stored procedure 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
  $$
  var rs = snowflake.execute( { sqlText: 
      `INSERT INTO table1 ("column 1") 
           SELECT 'value 1' AS "column 1" ;`
       } );
  return 'Done.';
  $$;

For more details about stored procedures, see Working with Stored Procedures.

Optional Parameters

All Languages

[ [ NOT ] NULL ]

Specifies whether the stored procedure can return NULL values or must return only NON-NULL values.

The default is NULL (i.e. the stored procedure can return NULL).

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 when called with the same input at different times (e.g. due to non-determinism and statefullness).

  • IMMUTABLE: When the procedure is called with the same input values, it always returns the same result. This guarantee is not checked. Specifying IMMUTABLE for a procedure that returns different values for the same input results 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 or . EXECUTE AS OWNER

Specifies whether the stored procedure executes 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 in the 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 procedures 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, see Understanding Caller’s Rights and Owner’s Rights Stored Procedures.

Default: OWNER

Usage Notes

  • For all stored procedures:

    • 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.

    • Stored procedures are not atomic; if one statement in a stored procedure fails, the other statements in the stored procedure are not necessarily rolled back. For information about stored procedures and transactions, see Transaction Management.

    • Regarding metadata:

      Attention

      Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata Fields in Snowflake.

    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.

  • For JavaScript stored procedures:

    • A JavaScript 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.

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.