Skip to main content
Kinetica provides support for basic SQL procedures, as an executable batch of SQL statements. A SQL procedure can be executed by two means:
  • on-demand - called directly by a user
  • scheduled execution - configured, upon creation, to execute at a user-specified interval
Even if a SQL procedure is configured for scheduled execution, it can still be executed directly by a user in on-demand fashion.
After the first run, the execution plan for all statements in the SQL procedure will be created and cached to improve performance on future executions. If there is an error executing any statement, the SQL procedure will stop immediately and report the error. If it is invoked via scheduled execution, an alert will be sent to the alert monitor, as there is no interactive session through which the error could be reported to a user. Any database modifications prior to the error will not be rolled back.
If any of the database objects referenced in the SQL procedure are dropped or modified, the SQL procedure will be dropped as well. This does not include any objects created by the SQL procedure that are later referenced by it.
The ability to manage & execute SQL procedures is available through SQL, using the following commands: For execute permission management, see:

​
Supported Statements

The following statement types are allowed within a SQL procedure:
  • SELECT
  • INSERT (inserting from data file not supported)
  • UPDATE
  • DELETE
  • CHECK PERMISSION
  • <CREATE | DROP | SHOW CREATE> SCHEMA
  • <CREATE | TRUNCATE | DROP | SHOW CREATE> TABLE
  • CREATE TABLE ... AS
  • <CREATE [MATERIALIZED] | REFRESH | DROP | SHOW CREATE> VIEW
  • DESCRIBE
  • EXECUTE FUNCTION
  • EXECUTE PROCEDURE
  • REFRESH MATERIALIZED VIEW
  • SHOW PROCEDURE
  • SHOW SECURITY [FOR <USER | ROLE>]
  • SHOW RESOURCE GROUP
Any unqualified table/view references made within the SQL procedure will be assumed to exist in the creator’s default schema at the time of creation. Those references will be set upon creation and never re-evaluated afterwards, regardless of the execution type of the SQL procedure, who executes it, or what EXECUTE AS option is specified.

​
CREATE PROCEDURE

Creates a new SQL procedure.
CREATE PROCEDURE Syntax
CREATE [OR REPLACE] PROCEDURE [<schema name>.]<procedure name> [()]
[LANGUAGE SQL]
BEGIN
    <sql statements>
END
[
    EXECUTE FOR EVERY <number> <SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S]>
        [STARTING AT '<YYYY-MM-DD [HH:MM[:SS]]>']
        [STOP AFTER < '<YYYY-MM-DD [HH:MM[:SS]]>' | <number> <SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S]> >]
]
[WITH OPTIONS (<stored procedure property name> = '<stored procedure property value>'[,...])]
When any of the source tables referenced by a SQL procedure is altered or dropped, the SQL procedure will also be dropped.

​
Parameters

<schema name>

Name of the schema in which this SQL procedure will be created.

<procedure name>

Name to give to the created SQL procedure; must adhere to the supported naming criteria for tables, and cannot be named the same as any existing table or view.

<sql statements>

Semicolon-separated list of supported SQL statements. If the final statement in the SQL procedure is an output-generating statement (SQL query, SHOW command, etc.), the output of that statement alone will be returned to the user; all other statements that generate output will be ignored.

OR REPLACE

Drop any existing SQL procedure with the same name before creating this one.

LANGUAGE

Optional language specification. Only SQL is supported at this time.

EXECUTE FOR EVERY

Allows specification of an interval in seconds, minutes, hours, or days, at which the SQL procedure should be executed. Fractional values for <number> are accepted.By default, the procedure will be run immediately and every specified interval after that; this can be modified with the following options:
  • STARTING AT: specify a date or timestamp at which the first execution should occur
  • STOP AFTER: specify a date, timestamp, or time interval after which executions should end

WITH OPTIONS

Optional indicator that a comma-delimited list of option/value assignments will follow. The following options are available:
OptionDescription
EXECUTE ASExecutes the SQL procedure as the given user with that user’s privileges.
If this user doesn’t exist at the time of execution, the SQL procedure will be executed as the creating user, and and, failing that, the system administration user.

​
Examples

To create a sqlp SQL procedure:
CREATE PROCEDURE Example
CREATE PROCEDURE example.sqlp
BEGIN
	CREATE OR REPLACE TABLE example.sqlp_table_tmp AS
	SELECT *
	FROM example.sqlp_table_ref;

	SELECT *
	FROM example.sqlp_table_tmp;
END
To create a sqlp_weekly SQL procedure that executes once per week:
CREATE PROCEDURE (Scheduled) Example
CREATE PROCEDURE example.sqlp_weekly
BEGIN
	CREATE OR REPLACE TABLE example.sqlp_table_tmp AS
	SELECT *
	FROM example.sqlp_table_ref;

	SELECT *
	FROM example.sqlp_table_tmp;
END
EXECUTE FOR EVERY 7 DAYS
STARTING AT '2019-01-01 00:00:00'

​
ALTER PROCEDURE

Alters the configuration of an existing SQL procedure. The following facets of a SQL procedure can be altered:

​
Set Execution User

A SQL procedure can have its execution user modified.
ALTER PROCEDURE SET EXECUTE AS Syntax
ALTER PROCEDURE [<schema name>.]<procedure name>
SET EXECUTE AS '<user name>'

​
Examples

To set the execution user of the sqlp_ea SQL procedure to spuser:
ALTER PROCEDURE SET EXECUTE AS Example
ALTER PROCEDURE example.sqlp_ea
SET EXECUTE AS 'spuser'

​
Set Execute Mode

The refresh mode of a SQL procedure can be modified.
ALTER PROCEDURE SET EXECUTE MODE Syntax
ALTER PROCEDURE [<schema name>.]<procedure name>
SET EXECUTE [FOR] EVERY <number> <SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S]>
    [STARTING AT '<YYYY-MM-DD [HH:MM[:SS]]>']
    [STOP AFTER < '<YYYY-MM-DD [HH:MM[:SS]]>' | <number> <SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S]> >]

​
Parameters

EXECUTE FOR EVERY

Specify an interval in seconds, minutes, hours, or days at which the SQL procedure should be executed.By default, the first execution interval will be one interval’s worth of time from the point at which the SQL procedure alteration was requested.

STARTING AT

Specify a date or timestamp at which refresh cycles should begin.

STOP AFTER

Specify a date, timestamp, or time interval after which refresh cycles should end.

​
Examples

To alter the sqlp_em SQL procedure to execute every 6 hours:
ALTER PROCEDURE Set Execution Mode Example
ALTER PROCEDURE example.sqlp_em
SET EXECUTE FOR EVERY 6 HOURS;
To alter a SQL procedure to execute every minute in January, 2026:
ALTER PROCEDURE Set Execution Start/Stop Example
ALTER PROCEDURE example.sqlp_em
SET EXECUTE FOR EVERY 1 MINUTE
    STARTING AT '2026-01-01 00:00:00'
    STOP AFTER 31 DAYS

​
EXECUTE PROCEDURE

SQL procedures can be executed on-demand.
EXECUTE PROCEDURE Syntax
EXEC[UTE] [PROCEDURE] [<schema name>.]<procedure name> [()]
If the final statement in the SQL procedure is an output-generating statement (SQL query, SHOW command, etc.), the output of that statement alone will be returned to the user; all other statements that generate output will be ignored. If there is an error executing any statement, the SQL procedure will stop immediately and report the error. Any database modifications prior to the error will not be rolled back.

​
Examples

To execute the sqlp SQL procedure:
EXECUTE PROCEDURE Example
EXECUTE PROCEDURE example.sqlp

​
DROP PROCEDURE

Removes an existing SQL procedure.
DROP PROCEDURE Syntax
DROP PROCEDURE [IF EXISTS] [<schema name>.]<procedure name>

​
Parameters

IF EXISTS

Optional error-suppression clause; if specified, no error will be returned if the given procedure does not exist.

<schema name>

Name of the schema containing the procedure to remove.

<procedure name>

Name of the procedure to remove.

​
Examples

To drop the sqlp SQL procedure:
DROP PROCEDURE Example
DROP PROCEDURE example.sqlp

​
SHOW PROCEDURE

Shows the content of one or all existing SQL procedures.
SHOW PROCEDURE Syntax
SHOW PROCEDURE < [<schema name>.]<procedure name> | * >

​
Parameters

<schema name>

Name of the schema containing the procedure to show.

<procedure name>

Name of the procedure whose content will be output; use * instead of schema/procedure name to output the DDL of all procedures.

​
Examples

To show the contents of the sqlp_weekly SQL procedure:
SHOW PROCEDURE Example
SHOW PROCEDURE example.sqlp_weekly
To output the DDL for all SQL procedures:
SHOW PROCEDURE (All SQL Procedures) Example
SHOW PROCEDURE *

​
Security

Permissions for managing procedures follow those for creating tables; e.g., if a user has the ability to create a table in a given schema, that user will also be able to create & drop procedures there. Executing a procedure requires either the implicit execute permission that is granted to the creator of a procedure, or explicit execute permission, which can be granted to or revoked from any user or role, irrespective of whether the target user or role has the appropriate access to the database objects referenced within the SQL procedure. When procedures are executed on-demand, they are run, by default, with invoker rights (caller permissions); while those run automatically by scheduled execution are executed with definer rights (creator permissions). These defaults can be overridden by creating the stored procedure with the EXECUTE AS option, which allows a user to be designated for permissions-checking when running the procedure. The EXECUTE AS user is automatically given EXECUTE permission on the corresponding SQL procedure. For scheduled execution runs, if the EXECUTE AS user loses EXECUTE permission on the procedure or doesn’t have permissions to run the commands within the procedure, the execution will fail accordingly. If the user doesn’t exist, the procedure will be executed with definer rights; and if the creator no longer exists, it will be executed as the system administration user. For on-demand runs, if the EXECUTE AS user doesn’t have permissions to run the commands within the procedure, the execution will fail accordingly. If the user doesn’t exist, the procedure will be executed with invoker rights. Execute permission on a procedure also allows the grantee to see the contents of the procedure.