SQL Procedures

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

CREATE PROCEDURE

Creates a new SQL procedure.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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.

For example, to create a sqlp SQL procedure:

1
2
3
4
5
6
7
8
9

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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:

• Execution User
• Execution Mode

1
2

ALTER PROCEDURE [<schema name>.]<procedure name>
SET EXECUTE AS '<user name>'

1
2

ALTER PROCEDURE example.sqlp_ea
SET EXECUTE AS 'spuser'

1
2
3
4

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]> >]

1
2

ALTER PROCEDURE example.sqlp_em
SET EXECUTE FOR EVERY 6 HOURS;

EXECUTE PROCEDURE

SQL procedures can be executed on-demand.

1

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.

For example, to execute the sqlp SQL procedure:

1

EXECUTE PROCEDURE example.sqlp

DROP PROCEDURE

Removes an existing SQL procedure.

1

DROP PROCEDURE [IF EXISTS] [<schema name>.]<procedure name>

For example, to drop the sqlp SQL procedure:

1

DROP PROCEDURE example.sqlp

SHOW PROCEDURE

Shows the content of an existing SQL procedure.

1

SHOW PROCEDURE [<schema name>.]<procedure name>

For example, to show the contents of the sqlp_weekly procedure:

1

SHOW PROCEDURE example.sqlp_weekly

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.