New post
Question
· Mar 15, 2022

Problems to call procedure and return of an outputvar

#InterSystems IRIS

Hello

I have trouble capturing the value from a stored procedures output variable from an MS SQL Database.

In the database querywindow of MS SQL manager, the procedure is called as:
declare @return As varchar (100)
Execute TheProcedure @return. @return then contains string of content.

From HC I call the procedure:

set RetValue = ""
set Inparm = "@return"
set Outparm = ## class (% ListOfDataTypes).% New ()

SET SQLQuery = "exec TheProcedure ?"
(have also tried
"{call TheProcedure (?)}"

SET tSC = ..Adapter.ExecuteProcedure (, .Outparm, SQLQuery, "o *", Inparm)
set RetValue = outparm.GetAt(1)

This constantly creates errors. I have tried different variants but nothing works.
Outparm is always null or empty.

Greatful for help with the right syntax!

Sincerely, Michael

Product version: IRIS 2021.1
Discussion (3)1
Log in or sign up to continue
Daniel Aguilar · Mar 15, 2022

Good morning Michael,

I haven't worked a lot with stored procedures but when I have worked with them, I did it like this.

TestSQLProc()
 New stmt,status,rSet,SQLQuery  

 SET SQLQuery = "SELECT PackageName.ClassName_ProcedureName('parameter')" 

 Set stmt = ##class(%SQL.Statement).%New()
 Set status = stmt.%Prepare(SQLQuery) 

 If status'=1 
 { 
    Set return="%Prepare failed:" Do $System.Status.DisplayError(status) Quit
 } 

 Set rSet = stmt.%Execute() While rSet.%Next()
 {
    U 0 W rSet."ColumnName"
 }    

Q

I think that your problem is that you're trying to get information using Call instead of Select.

I hope it helps you.

My best,

0 0
Michael Lundberg · Mar 15, 2022

Hi and thanks for your reply!

Since this is not a stored procedure in Iris database but in an MS SQL server, syntax does not work
"SELECT PackageName.ClassName_ProcedureName ('parameter')"

Normal call is Execute procedure name.

For example, when I call a procedure that does not use an output parameter, I use
"..Adapter.ExecuteQuery (.tResult, tQuery) Quit: $$$ ISERR (tStatus)"

However, because this procedure fills an output parameter, this call does not work. It then returns "Error! Function must return a value". So i suppose i need to use  ExecuteProcedure

Sincerely, Michael

0 0