Using Caché SQL
Defining and Using Stored Procedures
[Home] [Back] [Next]
InterSystems: The power behind what matters   

This chapter describes how to define and use stored procedures in Caché SQL. It discusses the following:

Like most relational database systems, Caché 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).
Unlike relational databases, Caché lets you define stored procedures as methods of classes. In fact, a stored procedure is nothing more than a class method that is made available to SQL. Within a stored procedure, you can use the full range of Caché object-based features.
You can determine if a procedure already exists using the $SYSTEM.SQL.ProcedureExists() method. This method also returns the procedure type: “function” or “query”.
Defining Stored Procedures
As with most aspects of Caché 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
Caché SQL supports the following commands to create a query:
Caché SQL supports the following commands to create a method or function:
The block of executable code specified within these commands can be written either in Caché 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.
The following rules govern the transformation of a schema name to valid package name:
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 tmp.test.sql Extends %RegisteredObject 
 {  ClassMethod myfunc(dummy As %String) As %String [ SqlProc ] 
    { /* method code */
      Quit "abc" }
SELECT tmp_test.sql_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 Caché (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.%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]
  &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
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
        SET name=rset.Name
        SET dob=rset.DOB
        SET Row = $LISTBUILD(name,dob)
        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:
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.Query, as described in this section.
It is often easier to implement the query if you choose type %Query (%Library.Query) instead of %SQLQuery (%Library.SQLQuery). 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
You can use stored procedures in two distinct ways:
When executing a stored procedure that takes an SQL function as a argument, invoke the stored procedure using CALL, as in the following example:
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.
xDBC does not support executing a stored procedure with an SQL function argument using either SELECT or CALL.
Stored Functions
A stored function is a method-based stored procedure that returns a single value. For example, the following class defines a stored function, Square, that returns the square of a given value:
Class MyApp.Utils Extends %Persistent [DdlAllowed]
ClassMethod Square(val As %Integer) As %Integer [SqlProc]
    Quit val * val
A stored function is simply a class method with the SqlProc keyword specified.
For a stored function, the ReturnResultsets keyword must either be not specified (the default) or prefaced by the keyword Not.
You can use a stored function within an SQL query as if it were a built-in SQL function. The name of the function is the SQL name of the stored function (in this case “Square”) qualified by the schema (package) name in which it was defined (in this case “MyApp”).
The following query uses the Square function:
SELECT Cost, MyApp.Utils_Square(Cost) As SquareCost FROM Products
If you define multiple stored functions within the same package (schema), you must make sure that they have unique SQL names.
The following example defines a table named Sample.Wages that has two defined data fields (properties) and two defined stored functions, TimePlus and DTime:
Class Sample.Wages Extends %Persistent [ DdlAllowed ]
  Property Name As %String(MAXLEN = 50) [ Required ];
  Property Salary As %Integer;
  ClassMethod TimePlus(val As %Integer) As %Integer [ SqlProc ]
   QUIT val * 1.5
  ClassMethod DTime(val As %Integer) As %Integer [ SqlProc ]
   QUIT val * 2
The following query uses these stored procedures to return the regular salary, time-and-a-half, and double time salary rates for each employee in the same table, Sample.Wages:
SELECT Name,Salary,
       Sample.Wages_TimePlus(Salary) AS Overtime,
       Sample.Wages_DTime(Salary) AS DoubleTime FROM Sample.Wages
The following query uses these stored procedures to return the regular salary, time-and-a-half, and double time salary rates for each employee in a different table, Sample.Employee:
SELECT Name,Salary,
       Sample.Wages_TimePlus(Salary) AS Overtime,
       Sample.Wages_DTime(Salary) AS DoubleTime FROM Sample.Employee
To execute a procedure, a user must have EXECUTE privilege for that procedure. Use the GRANT command or the %SYSTEM.SQL GrantObjPriv() 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.CheckPriv() method.
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 ([Home] > [Security Management] > [Users]) or Roles ([Home] > [Security Management] > [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. It provides a large number of properties.
When specified in Embedded SQL, INFORMATION.SCHEMA.ROUTINES requires the #include %occInclude macro preprocessor directive. This directive is not required for Dynamic SQL.
The following example returns the routine type (PROCEDURE or FUNCTION), schema name, and routine name for all routines in the schema “MyApp” in the current namespace:
SELECT Routine_Type,Routine_Schema,Routine_Name FROM INFORMATION_SCHEMA.ROUTINES WHERE Routine_Schema='MyApp'
You can display much of the same information as INFORMATION.SCHEMA.ROUTINES 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.

Send us comments on this page
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA