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.
The delimiters around the
procedure definitioncan 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.
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);
For more details about stored procedures, see:
CALLED ON NULL INPUTor .
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 INPUTwill 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.
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
IMMUTABLEfor a procedure that returns different values for the same input will result in undefined behavior.
COMMENT = 'string_literal'
Specifies a comment for the stored procedure, which is displayed in the DESCRIPTION column in the SHOW PROCEDURES output.
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.
- 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.
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.
procedure could allow a user to insert data into a table that the user didn’t have privileges to insert into directly.
INSERT only if certain requirements
For more examples, see Working with Stored Procedures.