Defining Stored Procedures

As with most aspects of InterSystems SQL, there are two ways of defining stored procedures: using DDL and using classes. These are described in the following sections.

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. Note that this command can be used to create.
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

Defining a Method Stored Procedure using Classes

Class methods can be exposed as Stored Procedures. These are ideal for actions that do not return data, such as a Stored Procedure that calculates a value and stores it in the database. Almost all classes can expose methods as Stored Procedures; the exception is generator classes, such as a data type class ([ClassType = datatype]). Generator classes do not have a runtime context. It is only valid to use a datatype context within the runtime of some other entity, such as a property.

To define a method stored procedure, simply define a class method and set its SqlProc keyword:

Class MyApp.Person Extends %Persistent [DdlAllowed]
{

/// This procedure finds total sales for a territory
ClassMethod FindTotal(territory As %String) As %Integer [SqlProc]
{
    // use embedded sql to find total sales
    &sql(SELECT SUM(SalesAmount) INTO :total 
            FROM Sales
            WHERE Territory = :territory
    )

    Quit total
}
}

After this class is compiled, the FindTotal() method will be projected to SQL as the stored procedure MyApp.Person_FindTotal(). You can change the name that SQL uses for the procedure using the SqlName keyword of the method.

The method uses a procedure context handler to pass the procedure context back and forth between the procedure and its caller (for example, the ODBC server). This procedure context handler is automatically generated by InterSystems IRIS (as %qHandle:%SQLProcContext) using the %sqlcontext object.

%sqlcontext consists of properties for the SQLCODE error status, the SQL row count, an error message, and so forth, which are set using the corresponding SQL variables, as follows:

  SET %sqlcontext.%SQLCode=SQLCODE
  SET %sqlcontext.%ROWCOUNT=%ROWCOUNT
  SET %sqlcontext.%Message=%msg

There is no need to do anything with these values, but their values will be interpreted by the client. The %sqlcontext object is reset before each execution.

The method should return no value.

The maximum number of user-defined methods for a class is 2000.

For instance, suppose there is a CalcAvgScore() method:

ClassMethod CalcAvgScore(firstname As %String,lastname As %String) [sqlproc]
{
  New SQLCODE,%ROWID
  &sql(UPDATE students SET avgscore = 
    (SELECT AVG(sc.score) 
     FROM scores sc, students st
     WHERE sc.student_id=st.student_id 
       AND st.lastname=:lastname
       AND st.firstname=:firstname)
     WHERE students.lastname=:lastname
       AND students.firstname=:firstname)

  IF ($GET(%sqlcontext)'= "") {
    SET %sqlcontext.%SQLCODE = SQLCODE
    SET %sqlcontext.%ROWCOUNT = %ROWCOUNT
  }
  QUIT
}

Defining a Query Stored Procedure using Classes

Many Stored Procedures that return data from the database can be implemented through the standard query interface. This approach works well as long as the procedure can be written in embedded SQL. Note the use of the Embedded SQL host variable to supply a value to the WHERE clause in the following example:

Class MyApp.Person Extends %Persistent [DdlAllowed]
{

    /// This procedure result set is the persons in a specified Home_State, ordered by Name
    Query ListPersons(state As %String = "") As %SQLQuery [ SqlProc ]
    {
        SELECT ID,Name,Home_State
        FROM Sample.Person
        WHERE Home_State = :state
        ORDER BY Name
    }
}

To expose a query as a Stored Procedure, either change the value of the SQLProc field to True in the Studio Inspector’s entry for the query or add the following “[ SqlProc ]” string to the query definition:

Query QueryName() As %SQLQuery( ... query definition ... ) 
    [ SqlProc ]

After this class is compiled, the ListPersons query will be projected to SQL as the stored procedure MyApp.Person_ListPersons. You can change the name that SQL uses for the procedure using the SqlName keyword of the query.

When MyApp.Person_ListPersons is called from SQL, it will automatically return the result set defined by the query’s SQL statement.

The following example is a stored procedure using a result set:

Class apc.OpiLLS.SpCollectResults1 [ Abstract ]
{

/// This SP returns a number of rows (pNumRecs) from WebService.LLSResults, and updates a property for each record
Query MyQuery(pNumRecs As %Integer) As %Query(ROWSPEC = "Name:%String,DOB:%Date") [ SqlProc ]
{
}

/// You put initial code here in the Execute method
ClassMethod MyQueryExecute(ByRef qHandle As %Binary, pNumRecs As %Integer) As %Status
{
    SET mysql="SELECT TOP ? Name,DOB FROM Sample.Person"       
    SET rset=##class(%SQL.Statement).%ExecDirect(,mysql,pNumRecs)
            IF rset.%SQLCODE'=0 {QUIT rset.%SQLCODE}
    SET qHandle=rset
    QUIT $$$OK
}

/// This code is called by the SQL framework for each row, until no more rows are returned
ClassMethod MyQueryFetch(ByRef qHandle As %Binary, ByRef Row As %List, 
                         ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = NewQuery1Execute ]
{
     SET rset=qHandle
     SET tSC=$$$OK 
      
     FOR {
        ///Get next row, quit if end of result set
        IF 'rset.%Next() {
                SET Row = "", AtEnd = 1
                SET tSC=$$$OK
                QUIT
                }
        SET name=rset.Name
        SET dob=rset.DOB
        SET Row = $LISTBUILD(name,dob)
        QUIT
        }         
        QUIT tSC
}

ClassMethod MyQueryClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = NewQuery1Execute ]
{
        KILL qHandle   //probably not necesary as killed by the SQL Call framework
        QUIT $$$OK
}

}

If it is possible to write the query as a simple SQL statement and create it through the Query Wizard, it is not necessary to know anything about the underlying methods that implement the query.

Behind the scenes, for each query the class compiler generates methods based on the name of the Stored Procedure, including:

stored-procedure-nameExecute()
stored-procedure-nameFetch()
stored-procedure-nameFetchRows()
stored-procedure-nameGetInfo()
stored-procedure-nameClose()

If the query is of type %SQLQuery, the class compiler automatically inserts some embedded SQL into the generated methods. Execute() declares and opens a stored cursor for the SQL. Fetch() is called repeatedly until it returns an empty row (SET Row=""). You can, optionally, also have Fetch() return an AtEnd=1 boolean flag to indicate that the current Fetch constitutes the last row and the next Fetch is expected to return an empty row. However, an empty row (Row="") should always be used as the test to determine when the result set has ended; Row="" should always be set when setting AtEnd=1.

FetchRows() is logically equivalent to repeated calls to Fetch(). GetInfo() is called to return details of the signature for the Stored Procedure. Close() closes the cursor.

All these methods are called automatically when a Stored Procedure is invoked from a client, but could in theory be called directly from ObjectScript running on the server.

To pass an object from the Execute() to a Fetch(), or from a Fetch() to the next invocation of Fetch(), you can set the query handler to the object reference (OREF) of the object you wish to pass. To pass multiple objects, you can set qHandle as an array:

  SET qHandle(1)=oref1,qHandle(2)=oref2

It is possible to create a result set stored procedure that is based on custom-written code (not an SQL statement).

The maximum number of user-defined queries for a class is 200.

Customized Class Queries

For complex queries, or for Stored Procedures that do not fit the query model, it is often necessary to customize the query by replacing some or all of its methods. You can use %Library.QueryOpens in a new tab, as described in this section.

It is often easier to implement the query if you choose type %Query (%Library.QueryOpens in a new tab) instead of %SQLQuery (%Library.SQLQueryOpens in a new tab). This generate the same five methods, but now the FetchRows() is simply a repeated invocation of Fetch() (%SQLQuery has some optimization that causes other behavior). GetInfo() simply gets information from the signature, so it is very unlikely that the code will need to be changed. This reduces the problem to creating class methods for each of the other three. Note that when the class is compiled, the compiler detects the presence of these methods, and does not overwrite them.

The methods need specific signatures: They all take a Qhandle (query handler) of type %Binary. This is a pointer to a structure holding the nature and state of the query. This is passed by reference to Execute() and Fetch() and by value to Close():

ClassMethod SP1Close(qHandle As %Binary) As %Status
{
   // ... 
}

ClassMethod SP1Execute(ByRef qHandle As %Binary,
    p1 As %String) As %Status
{
   // ...
}

ClassMethod SP1Fetch(ByRef qHandle As %Binary, 
    ByRef Row As %List, ByRef AtEnd As %Integer=0) As %Status
{
   // ...
}

Query SP1(p1 As %String) 
   As %Query(CONTAINID=0,ROWSPEC="lastname:%String") [sqlproc ]
{
}

The code usually includes declaration and use of an SQL cursor. Cursors generated from queries of type %SQLQuery automatically have names such as Q14. You must ensure that your queries are given distinct names.

The class compiler must find a cursor declaration, before making any attempt to use the cursor. Therefore the DECLARE statement (usually in Execute) must be in the same MAC routine as the Close and Fetch and must come before either of them. Editing the source directly, use the method keyword PLACEAFTER in both the Close and the Fetch definitions to make sure this happens.

Error messages refer to the internal cursor name, which typically has an extra digit. Therefore an error message for cursor Q140 probably refers to Q14.

Using Stored Procedures

Overview