Timothy Leavitt · Aug 17, 2016

What is the best pattern for reporting errors from an SqlProc?

Given a complex method flagged with [ SqlProc ] so it is available as an SQL stored procedure, what's the best way to report a non-system error detected in that method - say, for example, an error %Status - so that the SQL query calling it fails descriptively? Is it best to create and throw an exception, or are there special % variables involved (like in a trigger)? I haven't been able to find an answer in the documentation.

Thanks in advance!

2 0 2 248


Take a look at Sample.ResultSets in the samples namespace.  There are examples in both PersonSets and CustomSets methods.

I was going through and looking at my old questions without accepted answers - this is the right answer but a SAMPLES namespace is harder to come by these days, so posting the actual code for my own future reference:

ClassMethod PersonSets(name As %String = "", state As %String = "MA") As %Integer [ ReturnResultsets, SqlName = PersonSets, SqlProc ]
        // %sqlcontext is automatically created for a method that defines SQLPROC

        // SQL result set classes can be easily prepared using dynamic SQL. %Prepare returns a
        // status value. The statement's prepare() method can also be called directly. prepare() throws
        // an exception if something goes wrong instead of returning a status value.
    set tStatement = ##class(%SQL.Statement).%New()
    try {
        do tStatement.prepare("select name,dob,spouse from sample.person where name %STARTSWITH ? order by 1")
        set tResult = tStatement.%Execute(name)
        do %sqlcontext.AddResultSet(tResult)
        do tStatement.prepare("select name,age,home_city,home_state from sample.person where home_state = ? order by 4, 1")
        set tResult = tStatement.%Execute(state)
        do %sqlcontext.AddResultSet(tResult)
        set tReturn = 1
    catch tException {
        #dim tException as %Exception.AbstractException
        set %sqlcontext.%SQLCODE = tException.AsSQLCODE(), %sqlcontext.%Message = tException.SQLMessageString()
        set tReturn = 0
    quit tReturn