Yakov Berger · Nov 28, 2021

How To Get Stored Procedure Output?

I have a stored procedure (MSsql) which has one input and one output parameter.

and a Business Operation calling the SP

The following code executes the stored procedure successfully , but does not return the output.

what needs to be fixed?

set outparm = ""
set pResponse = ##class(Ens.StringResponse).%New()
SET SQLQuery2 = "{call s_sproc (?,?)}"
SET tSC = ..Adapter.ExecuteProcedure(,,SQLQuery2,"io*",pRequest.StringValue,outparm )
set pResponse.StringValue  = outparm 


0 128
Discussion (5)1
Log in or sign up to continue

pass outparm by reference    .outparm

  SET tSC = ..Adapter.ExecuteProcedure(,,SQLQuery2,"io*",pRequest.StringValue,.outparm )

Tried That before..still nothing.

in the event log i see "getting parm 2 / output 1 = '33'"

so the output exists...just evading me...

this is the method description  it seems that you should your call parameters differently

method ExecuteProcedure(ByRef pResultSnapshots As %ListOfObjects,
        Output pOutputParms As %ListOfDataTypes,
        pQueryStatement As %String,
        pIO As %String,
 as %Status [ Language = objectscript ]

The pOutputParms list returns the output values of all scalar output and input/output parameters.
This would result in this order

SET tSC = ..Adapter.ExecuteProcedure( , .outparmSQLQuery2 , "io*"pRequest.StringValue )

>> 1st par  - skipped
>> 2nd par = output byRef
>> 3rd par = SqlQuery
>> 4th par = io
>>par 5 ... query input parameters

This explains the log

the output i now get is  "11@%Library.ListOfDataTypes

in log i am getting "getting parm 2 / output 1 = '35'"

do i need to iterate through %ListOfDataTypes ?


i added 

set pResponse.StringValue  = outparm.GetAt(1)

and all's good.