CREATE PROCEDURE | Snowflake Documentation (original) (raw)

Creates a new stored procedure.

A procedure can be written in one of the following languages:

Note

When you want to create and call a procedure that is anonymous (rather than stored), use CALL (with anonymous procedure). Creating an anonymous procedure does not require a role with CREATE PROCEDURE schema privileges.

This command supports the following variants:

See also:

ALTER PROCEDURE, DROP PROCEDURE , SHOW PROCEDURES , DESCRIBE PROCEDURE, CALL,SHOW USER PROCEDURES

CREATE OR ALTER

Syntax

Java handler

You can create a stored procedure that either includes its handler code in-line, or refers to its handler code in a JAR file. For more information, see Keeping handler code in-line or on a stage.

For examples of Java stored procedures, see Writing Java handlers for stored procedures created with SQL.

For in-line stored procedures, use the following syntax:

CREATE [ OR REPLACE ] [ { TEMP | TEMPORARY } ] [ SECURE ] PROCEDURE ( [ [ DEFAULT ] ] [ , ... ] ) [ COPY GRANTS ] RETURNS { [ [ NOT ] NULL ] | TABLE ( [ [ , ... ] ] ) } LANGUAGE JAVA RUNTIME_VERSION = '' PACKAGES = ( 'com.snowflake:snowpark:' [, '' ...] ) [ IMPORTS = ( '' [, '' ...] ) ] HANDLER = '' [ EXTERNAL_ACCESS_INTEGRATIONS = ( [ , ... ] ) ] [ SECRETS = ('' = [ , ... ] ) ] [ TARGET_PATH = '' ] [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ] [ { VOLATILE | IMMUTABLE } ] -- Note: VOLATILE and IMMUTABLE are deprecated. [ COMMENT = '' ] [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER } ] AS ''

For a stored procedure that uses a precompiled handler, use the following syntax.

CREATE [ OR REPLACE ] [ { TEMP | TEMPORARY } ] [ SECURE ] PROCEDURE ( [ [ DEFAULT ] ] [ , ... ] ) [ COPY GRANTS ] RETURNS { [ [ NOT ] NULL ] | TABLE ( [ [ , ... ] ] ) } LANGUAGE JAVA RUNTIME_VERSION = '' PACKAGES = ( 'com.snowflake:snowpark:' [, '' ...] ) [ IMPORTS = ( '' [, '' ...] ) ] HANDLER = '' [ EXTERNAL_ACCESS_INTEGRATIONS = ( [ , ... ] ) ] [ SECRETS = ('' = [ , ... ] ) ] [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ] [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated. [ COMMENT = '' ] [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER } ]

JavaScript handler

For examples of JavaScript stored procedures, see Writing stored procedures in JavaScript.

CREATE [ OR REPLACE ] [ { TEMP | TEMPORARY } ] [ SECURE ] PROCEDURE ( [ [ DEFAULT ] ] [ , ... ] ) [ COPY GRANTS ] RETURNS [ NOT NULL ] LANGUAGE JAVASCRIPT [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ] [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated. [ COMMENT = '' ] [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER } ] AS ''

Python handler

For examples of Python stored procedures, see Writing stored procedures with SQL and Python.

For in-line stored procedures, use the following syntax:

CREATE [ OR REPLACE ] [ { TEMP | TEMPORARY } ] [ SECURE ] PROCEDURE ( [ [ DEFAULT ] ] [ , ... ] ) [ COPY GRANTS ] RETURNS { [ [ NOT ] NULL ] | TABLE ( [ [ , ... ] ] ) } LANGUAGE PYTHON RUNTIME_VERSION = '' PACKAGES = ( 'snowflake-snowpark-python[==]'[, '[==]' ... ]) [ IMPORTS = ( '' [, '' ...] ) ] HANDLER = '' [ EXTERNAL_ACCESS_INTEGRATIONS = ( [ , ... ] ) ] [ SECRETS = ('' = [ , ... ] ) ] [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ] [ { VOLATILE | IMMUTABLE } ] -- Note: VOLATILE and IMMUTABLE are deprecated. [ COMMENT = '' ] [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER }] AS ''

For a stored procedure in which the code is in a file on a stage, use the following syntax:

CREATE [ OR REPLACE ] [ { TEMP | TEMPORARY } ] PROCEDURE ( [ [ DEFAULT ] ] [ , ... ] ) [ COPY GRANTS ] RETURNS { [ [ NOT ] NULL ] | TABLE ( [ [ , ... ] ] ) } LANGUAGE PYTHON RUNTIME_VERSION = '' PACKAGES = ( 'snowflake-snowpark-python[==]'[, '[==]' ... ]) [ IMPORTS = ( '' [, '' ...] ) ] HANDLER = '.' [ EXTERNAL_ACCESS_INTEGRATIONS = ( [ , ... ] ) ] [ SECRETS = ('' = [ , ... ] ) ] [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ] [ { VOLATILE | IMMUTABLE } ] -- Note: VOLATILE and IMMUTABLE are deprecated. [ COMMENT = '' ] [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER } ]

Scala handler

You can create a stored procedure that either includes its handler code in-line, or refers to its handler code in a JAR file. For more information, see Keeping handler code in-line or on a stage.

For examples of Scala stored procedures, see Writing Scala handlers for stored procedures created with SQL.

For in-line stored procedures, use the following syntax:

CREATE [ OR REPLACE ] [ SECURE ] PROCEDURE ( [ [ DEFAULT ] ] [ , ... ] ) [ COPY GRANTS ] RETURNS { [ [ NOT ] NULL ] | TABLE ( [ [ , ... ] ] ) } LANGUAGE SCALA RUNTIME_VERSION = '' PACKAGES = ( 'com.snowflake:snowpark:' [, '' ...] ) [ IMPORTS = ( '' [, '' ...] ) ] HANDLER = '' [ TARGET_PATH = '' ] [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ] [ { VOLATILE | IMMUTABLE } ] -- Note: VOLATILE and IMMUTABLE are deprecated. [ COMMENT = '' ] [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER } ] AS ''

For a stored procedure that uses a precompiled handler, use the following syntax.

CREATE [ OR REPLACE ] [ SECURE ] PROCEDURE ( [ [ DEFAULT ] ] [ , ... ] ) [ COPY GRANTS ] RETURNS { [ [ NOT ] NULL ] | TABLE ( [ [ , ... ] ] ) } LANGUAGE SCALA RUNTIME_VERSION = '' PACKAGES = ( 'com.snowflake:snowpark:' [, '' ...] ) [ IMPORTS = ( '' [, '' ...] ) ] HANDLER = '' [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ] [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated. [ COMMENT = '' ] [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER } ]

Snowflake Scripting handler

For examples of Snowflake Scripting stored procedures, see Writing stored procedures in Snowflake Scripting.

CREATE [ OR REPLACE ] PROCEDURE ( [ [ DEFAULT ] ] [ , ... ] ) [ COPY GRANTS ] RETURNS { | TABLE ( [ [ , ... ] ] ) } [ NOT NULL ] LANGUAGE SQL [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ] [ { VOLATILE | IMMUTABLE } ] -- Note: VOLATILE and IMMUTABLE are deprecated. [ COMMENT = '' ] [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER } ] AS

Variant syntax

CREATE OR ALTER PROCEDURE

Creates a new procedure if it doesn’t already exist, or transforms an existing procedure into the procedure defined in the statement. A CREATE OR ALTER PROCEDURE statement follows the syntax rules of a CREATE PROCEDURE statement and has the same limitations as an ALTER PROCEDURE statement.

Alterations to the following are supported:

For more information, see CREATE OR ALTER PROCEDURE usage notes.

CREATE [ OR ALTER ] PROCEDURE ...

Required parameters

All languages

_name_ ( [ _argname_ _argdatatype_ [ DEFAULT _defaultvalue_ ] ] [ , ... ] )

Specifies the identifier (_name_), any input arguments, and the default values for any optional arguments for the stored procedure.

RETURNS _resultdatatype_ [ NOT NULL ]

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

As a practical matter, outside of a Snowflake Scripting block,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 withSnowflake Scripting.

Currently, the supported values for _language_ include:

Default: SQL.

AS _proceduredefinition_

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

Note the following:

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

Java

RUNTIME_VERSION = '_languageruntimeversion_'

The language runtime version to use. Currently, the supported versions are:

PACKAGES = ( '_snowparkpackagename_' [, '_packagename_' ...] )

A comma-separated list of the names of packages deployed in Snowflake that should be included in the handler code’s execution environment. The Snowpark package is required for stored procedures, so it must always be referenced in the PACKAGES clause. For more information about Snowpark, see Snowpark API.

By default, the environment in which Snowflake runs stored procedures includes a selected set of packages for supported languages. When you reference these packages in the PACKAGES clause, it is not necessary to reference a file containing the package in the IMPORTS clause because the package is already available in Snowflake. You can also specify the package version.

For the list of supported packages and versions for Java, query theINFORMATION_SCHEMA.PACKAGES view for rows, specifying the language. For example:

SELECT * FROM INFORMATION_SCHEMA.PACKAGES WHERE LANGUAGE = 'java';

To specify the package name and version number use the following form:

domain:package_name:version

To specify the latest version, specify latest for _version_.

For example, to include a package from the latest Snowpark library in Snowflake, use the following:

PACKAGES = ('com.snowflake:snowpark:latest')

When specifying a package from the Snowpark library, you must specify version 1.3.0 or later.

HANDLER = '_fullyqualifiedmethodname_'

Use the fully qualified name of the method or function for the stored procedure. This is typically in the following form:

com.my_company.my_package.MyClass.myMethod

where:

com.my_company.my_package

corresponds to the package containing the object or class:

package com.my_company.my_package;

Python

RUNTIME_VERSION = '_languageruntimeversion_'

The language runtime version to use. Currently, the supported versions are:

PACKAGES = ( '_snowparkpackagename_' [, '_packagename_' ...] )

A comma-separated list of the names of packages deployed in Snowflake that should be included in the handler code’s execution environment. The Snowpark package is required for stored procedures, so it must always be referenced in the PACKAGES clause. For more information about Snowpark, see Snowpark API.

By default, the environment in which Snowflake runs stored procedures includes a selected set of packages for supported languages. When you reference these packages in the PACKAGES clause, it is not necessary to reference a file containing the package in the IMPORTS clause because the package is already available in Snowflake. You can also specify the package version.

For the list of supported packages and versions for Python, query theINFORMATION_SCHEMA.PACKAGES view for rows, specifying the language. For example:

SELECT * FROM INFORMATION_SCHEMA.PACKAGES WHERE LANGUAGE = 'python';

Snowflake includes a large number of packages available through Anaconda; for more information, seeUsing third-party packages.

To specify the package name and version number use the following form:

To specify the latest version, omit the version number.

For example, to include the spacy package version 2.3.5 (along with the latest version of the required Snowpark package), use the following:

PACKAGES = ('snowflake-snowpark-python', 'spacy==2.3.5')

When specifying a package from the Snowpark library, you must specify version 0.4.0 or later. Omit the version number to use the latest version available in Snowflake.

HANDLER = '_fullyqualifiedmethodname_'

Use the name of the stored procedure’s function or method. This can differ depending on whether the code is in-line or referenced at a stage.

Scala

RUNTIME_VERSION = '_languageruntimeversion_'

The language runtime version to use. Currently, the supported versions are:

PACKAGES = ( '_snowparkpackagename_' [, '_packagename_' ...] )

A comma-separated list of the names of packages deployed in Snowflake that should be included in the handler code’s execution environment. The Snowpark package is required for stored procedures, so it must always be referenced in the PACKAGES clause. For more information about Snowpark, see Snowpark API.

By default, the environment in which Snowflake runs stored procedures includes a selected set of packages for supported languages. When you reference these packages in the PACKAGES clause, it is not necessary to reference a file containing the package in the IMPORTS clause because the package is already available in Snowflake. You can also specify the package version.

For the list of supported packages and versions for Scala, query theINFORMATION_SCHEMA.PACKAGES view for rows, specifying the language. For example:

SELECT * FROM INFORMATION_SCHEMA.PACKAGES WHERE LANGUAGE = 'scala';

To specify the package name and version number use the following form:

domain:package_name:version

To specify the latest version, specify latest for _version_.

For example, to include a package from the latest Snowpark library in Snowflake, use the following:

PACKAGES = ('com.snowflake:snowpark:latest')

Snowflake supports using Snowpark version 0.9.0 or later in a Scala stored procedure. Note, however, that these versions have limitations. For example, versions prior to 1.1.0 do not support the use of transactions in a stored procedure.

HANDLER = '_fullyqualifiedmethodname_'

Use the fully qualified name of the method or function for the stored procedure. This is typically in the following form:

com.my_company.my_package.MyClass.myMethod

where:

com.my_company.my_package

corresponds to the package containing the object or class:

package com.my_company.my_package;

Optional parameters

All languages

SECURE

Specifies that the procedure is secure. For more information about secure procedures, see Protecting Sensitive Information with Secure UDFs and Stored Procedures.

{ TEMP | TEMPORARY }

Specifies that the procedure persists for only the duration of the session in which you created it. A temporary procedure is dropped at the end of the session.

Default: No value. If a procedure is not declared as TEMPORARY, it is permanent.

You cannot create temporary procedures that have the same name as a procedure that already exists in the schema.

Note that creating a temporary procedure does not require the CREATE PROCEDURE privilege on the schema in which the object is created.

For more information about creating temporary procedures, see Temporary procedures.

[ [ 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:

Default: CALLED ON NULL INPUT

_VOLATILE | IMMUTABLE_

Deprecated

Attention

These keywords are deprecated for stored procedures. These keywords are not intended to apply to stored procedures. In a future release, these keywords will be removed from the documentation.

COMMENT = '_stringliteral_'

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

Default: stored procedure

EXECUTE AS OWNER or . EXECUTE AS CALLER or . EXECUTE AS RESTRICTED CALLER

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 EXECUTE AS ... isn’t specified, 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

COPY GRANTS

Specifies to retain the access privileges from the original procedure when a new procedure is created using CREATE OR REPLACE PROCEDURE.

The parameter copies all privileges, except OWNERSHIP, from the existing procedure to the new procedure. The new procedure will inherit any future grants defined for the object type in the schema. By default, the role that executes the CREATE PROCEDURE statement owns the new procedure.

Note:

Java

IMPORTS = ( '_stagepathandfilenametoread_' [, '_stagepathandfilenametoread_' ...] )

The location (stage), path, and name of the file(s) to import. You must set the IMPORTS clause to include any files that your stored procedure depends on:

Each file in the IMPORTS clause must have a unique name, even if the files are in different subdirectories or different stages.

TARGET_PATH = _stagepathandfilenametowrite_

Specifies the location to which Snowflake should write the JAR file containing the result of compiling the handler source code specified in the _proceduredefinition_.

If this clause is included, Snowflake writes the resulting JAR file to the stage location specified by the clause’s value. If this clause is omitted, Snowflake re-compiles the source code each time the code is needed. In that case, the JAR file is not stored permanently, and the user does not need to clean up the JAR file.

Snowflake returns an error if the TARGET_PATH matches an existing file; you cannot use TARGET_PATH to overwrite an existing file.

If you specify both the IMPORTS and TARGET_PATH clauses, the file name in the TARGET_PATH clause must be different from each file name in the IMPORTS clause, even if the files are in different subdirectories or different stages.

The generated JAR file remains until you explicitly delete it, even if you drop the procedure. When you drop the procedure you should separately remove the JAR file because the JAR is no longer needed to support the procedure.

For example, the following TARGET_PATH example would result in a myhandler.jar file generated and copied to thehandlers stage.

TARGET_PATH = '@handlers/myhandler.jar'

When you drop this procedure to remove it, you’ll also need to remove its handler JAR file, such as by executing theREMOVE command.

REMOVE @handlers/myhandler.jar;

EXTERNAL_ACCESS_INTEGRATIONS = ( _integrationname_ [ , ... ] )

The names of external access integrations needed in order for this procedure’s handler code to access external networks.

An external access integration specifies network rules andsecrets that specify external locations and credentials (if any) allowed for use by handler code when making requests of an external network, such as an external REST API.

SECRETS = ( '_secretvariablename_' = _secretname_ [ , ... ] )

Assigns the names of secrets to variables so that you can use the variables to reference the secrets when retrieving information from secrets in handler code.

Secrets you specify here must be allowed by the external access integrationspecified as a value of this CREATE PROCEDURE command’s EXTERNAL_ACCESS_INTEGRATIONS parameter

This parameter’s value is a comma-separated list of assignment expressions with the following parts:

For more information, including an example, refer to Using the external access integration in a function or procedure.

Python

IMPORTS = ( '_stagepathandfilenametoread_' [, '_stagepathandfilenametoread_' ...] )

The location (stage), path, and name of the file(s) to import. You must set the IMPORTS clause to include any files that your stored procedure depends on:

Each file in the IMPORTS clause must have a unique name, even if the files are in different subdirectories or different stages.

EXTERNAL_ACCESS_INTEGRATIONS = ( _integrationname_ [ , ... ] )

The names of external access integrations needed in order for this procedure’s handler code to access external networks.

An external access integration specifies network rules andsecrets that specify external locations and credentials (if any) allowed for use by handler code when making requests of an external network, such as an external REST API.

SECRETS = ( '_secretvariablename_' = _secretname_ [ , ... ] )

Assigns the names of secrets to variables so that you can use the variables to reference the secrets when retrieving information from secrets in handler code.

Secrets you specify here must be allowed by the external access integrationspecified as a value of this CREATE PROCEDURE command’s EXTERNAL_ACCESS_INTEGRATIONS parameter

This parameter’s value is a comma-separated list of assignment expressions with the following parts:

For more information, including an example, refer to Using the external access integration in a function or procedure.

Scala

IMPORTS = ( '_stagepathandfilenametoread_' [, '_stagepathandfilenametoread_' ...] )

The location (stage), path, and name of the file(s) to import. You must set the IMPORTS clause to include any files that your stored procedure depends on:

Each file in the IMPORTS clause must have a unique name, even if the files are in different subdirectories or different stages.

TARGET_PATH = _stagepathandfilenametowrite_

Specifies the location to which Snowflake should write the JAR file containing the result of compiling the handler source code specified in the _proceduredefinition_.

If this clause is included, Snowflake writes the resulting JAR file to the stage location specified by the clause’s value. If this clause is omitted, Snowflake re-compiles the source code each time the code is needed. In that case, the JAR file is not stored permanently, and the user does not need to clean up the JAR file.

Snowflake returns an error if the TARGET_PATH matches an existing file; you cannot use TARGET_PATH to overwrite an existing file.

If you specify both the IMPORTS and TARGET_PATH clauses, the file name in the TARGET_PATH clause must be different from each file name in the IMPORTS clause, even if the files are in different subdirectories or different stages.

The generated JAR file remains until you explicitly delete it, even if you drop the procedure. When you drop the procedure you should separately remove the JAR file because the JAR is no longer needed to support the procedure.

For example, the following TARGET_PATH example would result in a myhandler.jar file generated and copied to thehandlers stage.

TARGET_PATH = '@handlers/myhandler.jar'

When you drop this procedure to remove it, you’ll also need to remove its handler JAR file, such as by executing theREMOVE command.

REMOVE @handlers/myhandler.jar;

Access control requirements

A role used to execute this operation must have the followingprivileges at a minimum:

Privilege Object Notes
CREATE PROCEDURE Schema Required to create a permanent stored procedure. Not required when creating a temporary procedure that persists for only the duration of the session in which the procedure was created.
USAGE Procedure Granting the USAGE privilege on the newly created procedure to a role allows users with that role to call the procedure elsewhere in Snowflake.
USAGE External access integration Required on integrations, if any, specified when creating the procedure. For more information, seeCREATE EXTERNAL ACCESS INTEGRATION.
READ Secret Required on secrets, if any, specified when creating the procedure. For more information, seeCreating a secret to represent credentials and Using the external access integration in a function or procedure.
USAGE Schema Required on schemas containing secrets, if any, specified when creating the procedure. For more information, see Creating a secret to represent credentials and Using the external access integration in a function or procedure.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions onsecurable objects, see Overview of Access Control.

General usage notes

CREATE OR REPLACE statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.

For additional usage notes, see the following.

All handler languages

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.

Java

See the known limitations.

Javascript

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.

Python

See the known limitations.

Scala

See the known limitations.

CREATE OR ALTER PROCEDURE usage notes

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 return3.1415926;return 3.1415926;return3.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 varsqlcommand="INSERTINTOstproctesttable1(numcol1)VALUES("+FLOATPARAM1+")";trysnowflake.execute(sqlText:sqlcommand);return"Succeeded.";//Returnasuccess/errorindicator.catch(err)return"Failed:"+err;//Returnasuccess/errorindicator.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. }varsqlc​ommand="INSERTINTOstproct​estt​able1(numc​ol1)VALUES("+FLOATP​ARAM1+")";trysnowflake.execute(sqlText:sqlc​ommand);return"Succeeded.";//Returnasuccess/errorindicator.catch(err)return"Failed:"+err;//Returnasuccess/errorindicator. ;

For more examples, see Working with stored procedures.

In-line handler

Code in the following example creates a procedure called my_proc with an in-line Python handler function run. Through the PACKAGES clause, the code references the included Snowpark library for Python, whose Session is required when Python is the procedure handler language.

CREATE OR REPLACE PROCEDURE my_proc(from_table STRING, to_table STRING, count INT) RETURNS STRING LANGUAGE PYTHON RUNTIME_VERSION = '3.9' PACKAGES = ('snowflake-snowpark-python') HANDLER = 'run' AS defrun(session,fromtable,totable,count):session.table(fromtable).limit(count).write.saveastable(totable)return"SUCCESS"def run(session, from_table, to_table, count): session.table(from_table).limit(count).write.save_as_table(to_table) return "SUCCESS"defrun(session,fromt​able,tot​able,count):session.table(fromt​able).limit(count).write.savea​st​able(tot​able)return"SUCCESS";

Staged handler

Code in the following example creates a procedure called my_proc with an staged Java handler method MyClass.myMethod. Through the PACKAGES clause, the code references the included Snowpark library for Java, whose Session is required when Java is the procedure handler language. With the IMPORTS clause, the code references the staged JAR file containing the handler code.

CREATE OR REPLACE PROCEDURE my_proc(fromTable STRING, toTable STRING, count INT) RETURNS STRING LANGUAGE JAVA RUNTIME_VERSION = '11' PACKAGES = ('com.snowflake:snowpark:latest') IMPORTS = ('@mystage/myjar.jar') HANDLER = 'MyClass.myMethod';

Create and alter a procedure using the CREATE OR ALTER PROCEDURE command

Create an owner’s rights Python stored procedure with external access integrations and default OWNER privileges.

CREATE OR ALTER PROCEDURE python_add1(A NUMBER) RETURNS NUMBER LANGUAGE PYTHON HANDLER='main' RUNTIME_VERSION=3.10 EXTERNAL_ACCESS_INTEGRATIONS=(example_integration) PACKAGES = ('snowflake-snowpark-python') EXECUTE AS OWNER AS defmain(session,a):returna+1def main(session, a): return a+1defmain(session,a):returna+1;

Alter the stored procedure’s secrets and change the stored procedure to a caller’s rights procedure:

CREATE OR ALTER PROCEDURE python_add1(A NUMBER) RETURNS NUMBER LANGUAGE PYTHON HANDLER='main' RUNTIME_VERSION=3.10 EXTERNAL_ACCESS_INTEGRATIONS=(example_integration) secrets=('secret_variable_name'=secret_name) PACKAGES = ('snowflake-snowpark-python') EXECUTE AS CALLER AS defmain(session,a):returna+1def main(session, a): return a+1defmain(session,a):returna+1;