CREATE VARIABLE¶

Creates a new schema variable, a named, typed, persistent scalar value scoped to a schema.

See also:: ALTER VARIABLE , DROP VARIABLE , UNDROP VARIABLE , SHOW SCHEMA VARIABLES IN SCHEMA , DESCRIBE VARIABLE

Syntax¶

CREATE [ OR REPLACE ] VARIABLE [ IF NOT EXISTS ] <name> <data_type>
  [ DEFAULT <default_value> ]
  [ COMMENT = '<string_literal>' ]

Required parameters¶

name

Specifies the identifier for the variable. The identifier must be unique within the schema.

The identifier can be optionally qualified with the schema name (schema.variable_name) or with both the database and schema names (database.schema.variable_name).

For more details, see Identifier requirements.

data_type

Specifies the data type of the variable. All scalar Snowflake data types are supported. The value stored in the variable must be assignment-compatible with this type.

For the list of supported types, see Summary of data types.

Optional parameters¶

DEFAULT default_value

Specifies the initial value for the variable. The expression is evaluated at creation time and must produce a value compatible with data_type. If omitted, the variable’s initial value is NULL.

The default can be changed after creation with ALTER VARIABLE … SET DEFAULT.

COMMENT = 'string_literal'

Specifies a comment for the variable.

Default: No value

Access control requirements¶

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

Privilege	Object	Notes
CREATE VARIABLE	Schema
OWNERSHIP	Variable	Required to execute a CREATE OR REPLACE VARIABLE statement for an existing variable. OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege). Note that in a managed access schema, only the schema owner (i.e. the role with the OWNERSHIP privilege on the schema) or a role with the MANAGE GRANTS privilege can grant or revoke privileges on objects in the schema, including future grants.

Privilege

Object

Notes

CREATE VARIABLE

Schema

OWNERSHIP

Variable

Required to execute a CREATE OR REPLACE VARIABLE statement for an existing variable.

OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege).

Note that in a managed access schema, only the schema owner (i.e. the role with the OWNERSHIP privilege on the schema) or a role with the MANAGE GRANTS privilege can grant or revoke privileges on objects in the schema, including future grants.

Operating on an object in a schema requires at least one privilege on the parent database and at least one privilege on the parent 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 on securable objects, see Overview of Access Control.

Usage notes¶

Creating a variable automatically grants OWNERSHIP on that variable to the role that created it.
The maximum size of a variable’s value is 256 bytes.
Unquoted variable names are case-insensitive. For more information, see Identifier requirements.
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.

The OR REPLACE and IF NOT EXISTS clauses are mutually exclusive. They can’t both be used in the same statement.
CREATE OR REPLACE <object> 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.

Examples¶

Create a variable to hold a tax rate, with a default value:

CREATE VARIABLE myschema.tax_rate FLOAT DEFAULT 0.08;

Create a variable with a comment:

CREATE VARIABLE myschema.api_version VARCHAR
  DEFAULT 'v2'
  COMMENT = 'REST API version used by all stored procedures in this schema';

Create a variable using the fully qualified name:

CREATE VARIABLE mydb.myschema.environment VARCHAR DEFAULT 'production';

Create a variable only if it doesn’t already exist:

CREATE VARIABLE IF NOT EXISTS myschema.batch_cursor NUMBER DEFAULT 0;