· Apr 1

InterSystems Reports - Logi Report (23.2) - How to code a stored procedure that will return a result set to be used in a report

A report that I need to create - I need to write a class method that will execute a few queries and save the data to a class and then return the data in a result set that can be used in the Logi report as a Stored procedure.

I can create the class method that is a Stored procedure and do the queries and manipulate the data, however I am having an issue at the end of the method to return the fields back as a result set.

I have looked at the Intersystems' documentation for the stored procedures but not finding a good example for a class method that is a stored procedure which returns a result set (multiple rows and 8 columns)

If you have any examples or suggestions please let me know.


Product version: IRIS 2023.1
$ZV: IRIS for Windows (x86-64) 2023.1.2 (Build 450_0_22960U) Mon Dec 18 2023 18:47:09 EST
Discussion (3)3
Log in or sign up to continue

I have many integrations using JDBC stored procedure calls against MS SQL. 

  • Define Stored Procedure Class Structure that extends Extends (%Library.Persistent, %XML.Adaptor) [ Not ProcedureBlock, SqlRowIdPrivate ] for any Parameters (Properties) that need to be passed to the stored procedure
Class osuwmc.CPD.DataStructures.CheckProviderSpecialty Extends (%Library.Persistent, %XML.Adaptor) [ Not ProcedureBlock, SqlRowIdPrivate ]


Property DoctorNumber As %String(MAXLEN = 6);

Storage Default


<Data name="CheckProviderSpecialtyDefaultData">

<Value name="1">



<Value name="2">










  • Using a Custom Operation that uses the EnsLib.SQL.Outbound adapter, and a XData Message Map, I create Methods that use the Stored Procedure class structure defined and return EnsLib.SQL.Snapshot.
Include (EnsSQLTypes, %occODBC)

Class osuwmc.Epic.MFN.EpicMFNToCPDDBWriteDEV Extends Ens.BusinessOperation [ ClassType = "", ProcedureBlock ]


Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";

Parameter INVOCATION = "Queue";

Property InitDSN As %String;

Method OnInit() As %Status


    Set ..InitDSN = ..Adapter.DSN

    Kill $$$EnsRuntimeAppData(..%ConfigName)

    //Set ..Adapter.ConnectAttrs = "QueryTimeout:45" ; try this too just in case...

    Quit $$$OK

  • I call the execution of the store procedure using ..Adapter.ExecuteProcedureParmArray
Method CheckDoesProviderExists(pRequest As osuwmc.CPD.DataStructures.CheckDoesDoctorNumberExist, Output pResponse As EnsLib.SQL.Snapshot) As %Status


  set SPQuery = "{ ?= call InterfaceCheckDoctorNumber(?) }"

  set parm = 2

  set parm(1,"SqlType")=$$$SQLVARCHAR

  set parm(1,"IOTypes")=$$$SQLPARAMOUTPUT

  set parm(2)=pRequest.DoctorNumber

  set parm(2,"SqlType")=$$$SQLVARCHAR

  set parm(2,"IOTypes")=$$$SQLPARAMINPUT

  set tSC = ..Adapter.ExecuteProcedureParmArray(.CheckDoctor,.outputs,SPQuery,"oi",.parm)

  if tSC = 1


    set pResponse = CheckDoctor.GetAt(1)


  quit tSC


Let me know if you need additional help, but this should give you a good start.