· 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 


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

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