Defining and Using Stored Procedures

This topic describes how to define and use stored procedures in InterSystems SQL on InterSystems IRIS® data platform.

Overview

An SQL routine is an executable unit of code that can be invoked by the SQL query processor. There are two types of SQL routines: functions and stored procedures. Functions are invoked from any SQL statement that supports functionname() syntax. Functions accept some number of input directed arguments and return a single result value. Stored procedures accept some number of input, input-output, and output arguments. A stored procedure can be a user-defined function, returning a single value. A function can also be invoked by a CALL statement.

Like most relational database systems, InterSystems IRIS allows you to create SQL stored procedures. A Stored Procedure (SP) provides a callable routine that is stored in the database and can be invoked within an SQL context (for example, by using the CALL statement or via ODBC or JDBC).

Defining a Stored Procedure Using DDL

InterSystems SQL supports the following commands to create a query:

CREATE PROCEDURE can create a query that is always projected as a stored procedure. A query can return a single result set.
CREATE QUERY creates a query that can optionally be projected as a stored procedure. A query can return a single result set.

Note:

In InterSystems SQL, a table-valued function is the same as a class query projected as a stored procedure. You can therefore use either CREATE PROCEDURE or CREATE QUERY to create a table-valued function.

InterSystems SQL supports the following commands to create a method or function:

CREATE PROCEDURE can create a method that is always projected as a stored procedure. A method can return a single value, or one or more result sets.
CREATE METHOD can create a method that can optionally be projected as a stored procedure. A method can return a single value, or one or more result sets.
CREATE FUNCTION can create a function procedure that can optionally be projected as a stored procedure. A function can return a single value.

The block of executable code specified within these commands can be written either in InterSystems SQL or ObjectScript. You can include Embedded SQL within an ObjectScript code block.

SQL to Class Name Transformations

When you use DDL to create a stored procedure, the name you specify is transformed into a class name. If the class does not exist, the system creates it.

If the name is unqualified and no FOR clause is provided: the system-wide default schema name is used as the package name, followed by a dot, followed by a generated class name consisting of the string ‘func’, ‘meth’, ‘proc’, or ‘query’, followed by the SQL name stripped of punctuation characters. For example, the unqualified procedure name Store_Name results in a class name such as the following: User.procStoreName. This procedure class contains the method StoreName().
If the name is qualified and no FOR clause is provided: the name of the schema is converted to a package name, followed by a dot, followed by the string ‘func’, ‘meth’, ‘proc’, or ‘query’, followed by the SQL name stripped of punctuation characters. If necessary, the specified package name is converted to a valid package name.
If the name is qualified and a FOR clause is provided: the qualified class name specified in the FOR clause overrides the schema name specified in the function, method, procedure, or query name.
SQL stored procedure names follow identifier naming conventions. InterSystems IRIS strips punctuation characters from the SQL name to generate unique class entity names for the procedure class and its class methods.

The following rules govern the transformation of a schema name to valid package name:

If the schema name contains an underscore, this character is converted to a dot, denoting a subpackage. For example, the qualified name myprocs.myname creates the package myprocs. The qualified name my_procs.myname creates the package my containing the subpackage procs.

The following example shows how the punctuation differs in a class name and its SQL invocation. It defines a method with a class name containing two dots. When invoked from SQL, the example replace the first dot with an underscore character:

Class Sample.ProcTest Extends %RegisteredObject 
 {  ClassMethod myfunc(dummy As %String) As %String [ SqlProc ] 
    { /* method code */
      Quit "abc" }
 }

SELECT Sample.ProcTest_myfunc(Name)
FROM Sample.Person

Using Stored Procedures

You can use stored procedures in two distinct ways:

You can invoke a stored procedure using the SQL CALL statement; see CALL for more details.
You can use a stored function (that is, a method-based stored procedure that returns a single value) as if it were a built-in function within an SQL query.

Note:

When executing a stored procedure that takes an SQL function as an argument, invoke the stored procedure using CALL, as in the following example:

CALL sp.MyProc(CURRENT_DATE)

A SELECT query does not support executing a stored procedure with an SQL function argument. SELECT does support executing a stored function with an SQL function argument.

You cannot execute a stored procedure with an SQL function argument using either SELECT or CALL over a driver connection.

Privileges

To execute a procedure, a user must have EXECUTE privilege for that procedure. Use the GRANT command or the $SYSTEM.SQL.Security.GrantPrivilege() method to assign EXECUTE privilege for a specified procedure to a specified user.

You can determine if a specified user has EXECUTE privilege for a specified procedure by invoking the $SYSTEM.SQL.Security.CheckPrivilege() method.

For more information about which class queries check privileges, refer to SQL Users, Roles, and Privileges.

To list all the procedures for which a user has EXECUTE privilege, go to the Management Portal. From System Administration select Security, then select either Users or Roles. Select Edit for the desired user or role, then select the SQL Procedures tab. Select the desired Namespace from the drop-down list.

Listing Procedures

The INFORMATION.SCHEMA.ROUTINES persistent class displays information about all routines and procedures in the current namespace.

The following example returns the routine name, method or query name, routine type (PROCEDURE or FUNCTION), routine body (SQL=class query with SQL, EXTERNAL=not a class query with SQL), the return data type, and the routine definition for all routines in the schema “Sample” in the current namespace:

SELECT ROUTINE_NAME,METHOD_OR_QUERY_NAME,ROUTINE_TYPE,ROUTINE_BODY,SQL_DATA_ACCESS,IS_USER_DEFINED_CAST,
DATA_TYPE||' '||CHARACTER_MAXIMUM_LENGTH AS Returns,NUMERIC_PRECISION||':'||NUMERIC_SCALE AS PrecisionScale,
ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='Sample'

The INFORMATION.SCHEMA.PARAMETERS persistent class displays information about input and output parameters for all routines and procedures in the current namespace.

The following example returns the routine name, parameter name, whether it is an input or output parameter, and the parameter data type information for all routines in the schema “Sample” in the current namespace:

SELECT SPECIFIC_NAME,PARAMETER_NAME,PARAMETER_MODE,ORDINAL_POSITION,
DATA_TYPE,CHARACTER_MAXIMUM_LENGTH AS MaxLen,NUMERIC_PRECISION||':'||NUMERIC_SCALE AS PrecisionScale
FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA='Sample'

You can display much of the same information for a single procedure using the Catalog Details tab in the Management Portal SQL Interface. The Catalog Details for a procedure include the procedure type (query or function), class name, method or query name, the description, and the number of input and output parameters. The Catalog Details Stored Procedure Info display also provides an option to run the stored procedure.